Adapter.Update

[jsurpless]

Hi all

I've successfully used the following code to populate a datatable from my MS Access database

strMSAccess_Connection_SQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFilePath & ";Persist Security Info=True"
MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)

MSAccess_Connection.Open()

strDatabaseTable_SQL = "SELECT * FROM [" & strDatabaseTable & "]"

If (strDatabaseTable_SQLWhere <> "") Then

  strDatabaseTable_SQL = strDatabaseTable_SQL & " " & strDatabaseTable_SQLWhere

End If

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)
       MSAccess_Database_Adapter.Fill(MSAccess_DatabaseTable)

Now, I'd like to use a similar method to push changes back to the database, having done them to the datatable...

I was thinking I'd be able to do something like this but it doesn't seem to work...

strMSAccess_Connection_SQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFilePath & ";Persist Security Info=True"
MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)

MSAccess_Connection.Open()

strDatabaseTable_SQL = "SELECT * FROM [" & strDatabaseTable & "]"

If (strDatabaseTable_SQLWhere <> "") Then

  strDatabaseTable_SQL = strDatabaseTable_SQL & " " & strDatabaseTable_SQLWhere

End If

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)

MSAccess_Database_Adapter.AcceptChangesDuringUpdate = True

MSAccess_Database_Adapter.Update(MSAccess_DatabaseTable)

Any thoughts?

 

[Tom]

There is no update or insert sql statements, your just reassigning the same select statement again.

 

[jsurpless]

OK but then what would I do in order to get the changes (which I have already made within the datatable) down to the database table?

Thanks!

 

[Tom]

A dataadapter accepts Select, Insert, Update & Delete commands, each should be assigned appropiately. You are assigning only the select statement.

 

[jsurpless]

Are you referring to the 'SELECT' statement I have in my SQL string?

I'm sorry but I'm not very experienced with this... is what i'm trying to do not possible? By this, I mean - making *all* the changes to the datatable and then pushing that down?

Could I ask you to give some code samples?

Many thanks!

 

[Tom]

Take a look at the OledbDataAdapter in the helpfile, it will have plenty of examples. Take notice of the
OledbDataAdapter.SelectCommand,
OledbDataAdapter.UpdateCommand,
OledbDataAdapter.InsertCommand,
OledbDataAdapter.DelectCommand
properties...

 

[jsurpless]

OK, I am seeing that now... but correct me if I am wrong but I'd have to execute this on every change I want to make, no? As such, I'd need to write to the database for each AutoCAD attribute I want...

What does the .Update do? From the description, it seems to indicate that the datatable keeps track of what changes (adds, deletes, etc) that are made to it and .Update just pushes that down...

Or am I misinterpreting it?

 

[Tom]

adapter.update() will call whatever commands are appropiate. If you have records that have been modified, it will call you UpdateCommand, if you have newly added records it will call your InsertCommand, if you have deleted records from your datatable it will call the DeleteCommand and now delete it from the actual database....

There are some objects and places that will auto-generate this coding for you but I think your confused enough so im not even going to mention them...

 

[jsurpless]

OK... so .Update does what I thought it does, no?

How am I using .Update incorrectly?

My intent was to load the information into the datatable, loop through its datarows (making changes, adds, deletes) and then re-open the connection to my database, pushing the modified datatable down...

Am I just completely misunderstanding something here?

 

[jsurpless]

Another thought... I noticed that in the code where I was making the changes to the datarow, I was also setting .AcceptChanges... as I understand it, this clears .Modified or the like no?

Having said that, I commented it out and am now getting the following error

Exception Type : System.InvalidOperationException

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

on line

MSAccess_Database_Adapter.Update(MSAccess_DatabaseTable)

I seem to be making *some* progress?

 

[cjard]

you'd make a lot more if you'd read the DW2 link in my signature, section Creating a Simple Data App. Then also read the DNU link

 

[jsurpless]

Thanks for the suggestion but I should mention that i've come across your posts and as such, I've consulted the links in your signature... I don't entirely understand what they're describing (if that wasn't painfully obvious ;-))

Looking over what you recommended again, it discusses using TableAdapters... should I not be using OleDbAdapters? I'm using that to read my database into my datatable but am I complicating things by using it to output? Would the TableAdapter work the way I want, without having to use .UpdateCommand, etc?

Also, it seems that the method described in the Simple Data App involves selecting a fixed database prior to compilation - is that correct? If so, I should mention that I don't believe I can do that as my database is not fixed...

Thanks again

 

[Tom]

No way a few posts in any thread are going to detail everything there is to working with databases. If the help file and numerous example links confuse you, I could suggest programming books geared toward VB & databases that would be beneificial.

 

[jsurpless]

Definitely not looking for everything to be detailed... as I mentioned, what I'm really curious about is whether or not I'm even on the right track...

I look over that information linked and there seem to be many different approaches... which would you recommend? The OleDbAdapter, TableAdapter, SQL, etc?

Which approach should I focus on? What's confusing me is that I believe I have records that are modified, as I can loop through the datatable and see the RowState property...

Pardon my lack of understanding, as I'm not a programmer by trade - I'm a process engineer who's attempting to program an application...

 

[Tom]

As with all things programming there are usually many options for accomplishing the same thing. Some things auto-generate some of the code/commands for ya but your still need to understand how it all works together.

dim cmd as OleDbCommand
Dim da as New OleDbDataAdapter

cmd = new OleDbCommand
Cmd.CommandText = "Select * From tblBlah"
da.SelectCommand = cmd

cmd = new OleDbCommand
Cmd.CommandText = "Update tblBlah Set col1 = 'blah' Where..."
da.UpdateCommand = cmd

etc
etc

da.Update(myDataset)