Writing data to an Access database

[Guggly]

This is probably a real dumb question, but I can't seem to figure out how to actually write data to an Access database I'm using. I can read the data fine using the following code, where QuerySQL is an SQL Select Statement.

If objConn.State.Equals(ConnectionState.Closed) Then OpenDatabase()

objAdapter = New OleDbDataAdapter(QuerySQL, objConn)

objDataSet.Tables.Add("tblMain")

objAdapter.Fill(objDataSet.Tables("tblMain"))

I can read the data fine and even write to it by doing something like this:

objDataSet.Tables("tblMain").Rows(0).Item(0) = 1

When I run the .AcceptChanges method, everything seems to work fine, but it's not actually writing to the underlying database. What do I need to do in order to actually write the data?

Thanks! -- Mike

 

[dalchri]

You'll need to create an UpdateCommand, InsertCommand, and DeleteCommand for objAdapter and then call

objAdapter.Update(objDataSet.Tables("tblMain"))

Don't worry about calling AcceptChanges, the DataAdapter does this when you call Update.

 

[jubble]

You can also use the CommandBuilder object which means you won't have to spend time building parameters to pass back to the database
Keep in mind though:
1) To use the CommandBuilder, one of the columns in the Select statement must be a primary key column
2) Don't create and open the connection object yourself, Let the dataadapter do this for you. I've come across an issue ('by design' no doubt)where the data won't be updated in the database if you explicitly create new connection objects in seperate methods

There's a MS example here

http://msdn.microsoft.com/library/d...emdatacommondbdataadapterclassupdatetopic.asp

but it's quite basic and doesn't change data values in seperate methods which would be the case in most scenarios (eg load data to datagrid, user changes value, clicks update)

 

[jubble]

Don't create and open the connection object yourself

just to elaborate as I didn't explain this too well. Create a modular connection and instantiate it ONCE in the the method that retrieves the data...but don't use it to open the connection...and don't instantiate it anywhere else

 

[Guggly]

Thanks dalchri and jubble. Between your two suggestions I managed to figure something out and thus far it seems to be working