[RESOLVED] working with a dataset

[daviddoria]

I wrote functions called "GetDataSet" and "PutDataSet" that i pass a query string to and they open my database, create a dataAdapter, use the adapter to .fill a data set, and return/write that data set

this is working fine, however, when i want to make changes to a data set through a DataGridView control, my PutDataSet doesn't work. I think its because i need the same data adapter? Is this a bad way to do this / how can i pass the data adapter around, for example, so i can make a 'save' button and it will write whatever is in the datagridview to my database?

below are the functions, any help would be appreciated!

thanks,

David

Public Function GetDataSet(ByVal TableName As String, ByVal FieldName As String, ByVal WhereString As String) As DataSet
Dim MyDataSet As New DataSet
Dim MyDatabaseConnection As New OleDb.OleDbConnection
Dim MyDataAdapter As OleDb.OleDbDataAdapter

MyDatabaseConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & DatabaseSource

Dim MyWhereString As String
If WhereString = "" Then
MyWhereString = ""
Else
MyWhereString = " WHERE " & WhereString
End If

Dim AssociateQuery As String = "SELECT " & FieldName & " FROM " & TableName & MyWhereString

'MsgBox(AssociateQuery)
MyDataAdapter = New OleDb.OleDbDataAdapter(AssociateQuery, MyDatabaseConnection)
MyDataAdapter.Fill(MyDataSet, TableName)

Return MyDataSet
End Function

Public Function PutDataSet(ByVal DataToPut As DataSet, ByVal TableName As String) As Boolean
Dim MyDataSet As New DataSet
Dim MyDatabaseConnection As New OleDb.OleDbConnection
Dim MyDataAdapter As OleDb.OleDbDataAdapter

MyDatabaseConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & DatabaseSource

'get all employees and add them to the list
'Dim AssociateQuery As String = "SELECT AssociateName FROM associate where type='" & TypeToLoad & "'"
Dim MyQuery As String = "SELECT * FROM " & TableName

MyDataAdapter = New OleDb.OleDbDataAdapter(MyQuery, MyDatabaseConnection)
MyDataAdapter.Fill(MyDataSet, TableName)

Dim MyBuilder As New OleDb.OleDbCommandBuilder
MyBuilder.DataAdapter = MyDataAdapter

MyDataAdapter.UpdateCommand = MyBuilder.GetUpdateCommand

MyDataAdapter.Update(DataToPut, TableName)

Return True
End Function

[jmcilhinney]

Why are you calling Fill in your PutDataSet method? You're trying to write data to the database so why are you retrieving data from the database? Also, do NOT do this:

MyDataAdapter.UpdateCommand = MyBuilder.GetUpdateCommand

The whole point of the CommandBuilder is that it generates the required SQL statements on demand. You only ever use the GetUpdateCommand method, et al. if you need to change them in some way. You don't so don't use them.

Also, please put your code in [Highlight=VB] tags and don't say that something "doesn't work" without giving an explanation what actually does happen.