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?

 

[jsurpless]

Definitely try to stay away from just using auto-generated code without understanding it - that doesn't really accomplish much...

I believe I understand how you're specifying commands and such - for instance, is this code

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

basically the same as my code

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)

I see how you're basically loading the data into the adapter and then modifying it... and then dumping it back onto the MyDataSet - is that a linked dataset as how 'My Simple App' describes it?

My datatable is not linked to my access database - I establish a connection, fill it and then close the connection... I then proceed to make changes to the datatable (which show up as RowState = Modified) and then I'd like to re-open the database connection and update the table...

Is that possible?

 

[jsurpless]

Can you tell me if my understanding of the DataAdapter and Datatable is correct or if I am way off base?

On the read side, I see the adapter as establishing the connection to the specified table via the specified connection... that's done via the .SelectCommand, no?

This "pulls" the data from the database into the dataadapter - where I can't do anything until I put it into either a dataset or a datatable, right?

Conversely, once I've worked with the datatable - made my add/modifys/deletes/etc - I can use the dataadapter which is once again connected to my database via the .Update command... but I guess I need to tell it what to update via the .UpdateCommand?

 

[Tom]

The select statement only retrieves records from the database. It does not tell it how to update, delete or insert new records to the database....

 

[jsurpless]

I understand that... haven't I done that in my datatable?

 

[Tom]

No.... You have nothing that tells it how to update, delete or insert. Thats why you need seperate statements in each of those properties.

 

[jsurpless]

I guess this is what is confusing me... I've verified that the datatable has changes made to it...

I guess it seems that I need to "transfer" that those changes have occurred from the datatable to the adapter?

I found this in the MSDN

Public Function CreateCommandAndUpdate( _
    ByVal connectionString As String, _
    ByVal queryString As String) As DataSet

    Dim dataSet As DataSet = New DataSet

    Using connection As New OleDbConnection(connectionString)
        connection.Open()
        Dim adapter As New OleDbDataAdapter()

        adapter.SelectCommand = New OleDbCommand( _
            queryString, connection)

        Dim builder As OleDbCommandBuilder = _
            New OleDbCommandBuilder(adapter)

        adapter.Fill(dataSet)

        ' Code to modify the data in the DataSet here. 

        ' Without the OleDbCommandBuilder this line would fail.
        builder.GetUpdateCommand()
        adapter.Update(dataSet)
    End Using
    Return dataSet
End Function

As I see it, I'm doing everything except the OleDbCommandBuilder stuff - from what i can tell, this is what transfers the "knowledge" of the changes to the adapter's .UpdateCommand via the .GetUpdateCommand?

I'm not seeing a "link" between the two but I guess it's just there in the underpinnings of .NET?

 

[Tom]

Ok to make things a bit more confusing, yes a commandbuilder can be used to "autogenerate" those commands and assign them to the appropiate spots of a DataAdapter.

But your using a completly different DataAdapter. Where is a command builder assigned to MSAccess_Database_Adapter?

 

[jsurpless]

OK... so that's what you were referring to earlier when you mentioned things that can auto-generate the UpdateCommand

I'm not doing anything with the Commandbuilder - that's what I'm working on right now...

Quick question... the example shows the builder BEFORE the .Fill - does that establish the link to the adapter and once I .Fill the datatable, the link is extended to the datatable...

I ask because I'm not seeing any explicit action to get the changes from the table... it's just sort of done...

 

[Tom]

Yes with the dataadapter that your are actually using, you can create a CommandBuilder and attach it to that dataadapter (MSAccess_Database_Adapter), it will then auto generate the additional sql statements. (still think its much better to understand what its actually generating first though), then when you call Adapter.Update(yourdataset) it should work.

That aside, if CreateCommandAndUpdate is actually in your project, I would completly delete it...

 

[jsurpless]

That subroutine is not actually in my application; I just cited it as an example...

I would definitely work to understand it during runtime - I'd be personally curious to see the .UpdateCommand value before .Update is run...

So do I have create it before I make the changes to the 'attached' datatable that I fill from the adapter?

 

[cjard]

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 ;-))

I advocate starting with the Simple Data App because (from memory) it describes a lot of stuff - connecting a db, related tables, loading and saving data etc.. if you do it first and you find it works, then you do it again with your eyes open and curious mind active, you learn a lot from jsut the one tutorial.. It also links off to more in depth discussion of the advanced stuff

Looking over what you recommended again, it discusses using TableAdapters... should I not be using OleDbAdapters?

TableAdapter is an abstraction created for you by the IDE that uses an OleDbAdapter internally (if youre using an access db), a SqlAdapter (if youre using sql server) or an OracleDataAdapter (of youre using oracle)
In OO terms the concept of abstraction is important; work with a consistent set of commands

I'm using that to read my database into my datatable but am I complicating things by using it to output?

Scrap it all, the reading stuff too. TableAdapter takes all that complex code you wrote and writes it itself, and then hides it. Information Hiding is a concept central to OO abstraction
You can see the code if you really want, using Show All Files

Would the TableAdapter work the way I want, without having to use .UpdateCommand, etc?

I wouldnt have told you it did if it would not

Also, it seems that the method described in the Simple Data App involves selecting a fixed database prior to compilation - is that correct?

Er.. yes. Typically most apps use a database of a known schema. if youre writing a query tool then sure, maybe you shouldnt use this route, but are you really working with a DB whose schema changes from one to the next? How can you be writing SQLs then?

 

[jsurpless]

Unfortunately, my tool is intended to be used without a specific database; this is because I am developing it for company-wide use across many projects - all of which use their own database...

Looking at the example of the TableAdapter

Try
    Me.Validate()
    Me.CustomersBindingSource.EndEdit()
    Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
    MsgBox("Update successful")

Catch ex As Exception
    MsgBox("Update failed")
End Try

What is 'Me'?

 

[jsurpless]

I was able to load up my datatable, make some changes to it and attempt to set the .UpdateCommand via

MSAccess_Database_Adapter.UpdateCommand = MSAccess_Database_Builder.GetUpdateCommand()
MSAccess_Database_Adapter.Update(MSAccess_DatabaseTable)

I verified that the .UpdateCommand changes but it changes to this?!?!?

UPDATE Instrument Loop Drawings for ADG Export SET Drawing_FileName = ?, TITLE_1 = ?, TITLE_2 = ?, TITLE_3 = ?, TITLE_4 = ?, TA_CABINET = ?, IO_CABINET = ?, CURRENT_DATE = ?, CURRENT_REV = ?, DATE0 = ?, DES0 = ?, REW0 = ?, APP0 = ?, REASON0 = ?, DATE1 = ?, DES1 = ?, REW1 = ?, APP1 = ?, REASON1 = ?, DATE2 = ?, DES2 = ?, REW2 = ?, APP2 = ?, REASON2 = ?, DATE3 = ?, DES3 = ?, REW3 = ?, APP3 = ?, REASON3 = ?, DATE4 = ?, DES4 = ?, REW4 = ?, APP4 = ?, REASON4 = ?, DATE5 = ?, DES5 = ?, REW5 = ?, APP5 = ?, REASON5 = ?, DATE6 = ?, DES6 = ?, REW6 = ?, APP6 = ?, REASON6 = ?, DATE7 = ?, DES7 = ?, REW7 = ?, APP7 = ?, REASON7 = ? WHERE ((Drawing_FileNameID = ?) AND ((? = 1 AND Drawing_FileName IS NULL) OR (Drawing_FileName = ?)) AND ((? = 1 AND TITLE_1 IS NULL) OR (TITLE_1 = ?)) AND ((? = 1 AND TITLE_2 IS NULL) OR (TITLE_2 = ?)) AND ((? = 1 AND TITLE_3 IS NULL) OR (TITLE_3 = ?)) AND ((? = 1 AND TITLE_4 IS NULL) OR (TITLE_4 = ?)) AND ((? = 1 AND TA_CABINET IS NULL) OR (TA_CABINET = ?)) AND ((? = 1 AND IO_CABINET IS NULL) OR (IO_CABINET = ?)) AND ((? = 1 AND CURRENT_DATE IS NULL) OR (CURRENT_DATE = ?)) AND ((? = 1 AND CURRENT_REV IS NULL) OR (CURRENT_REV = ?)) AND ((? = 1 AND DATE0 IS NULL) OR (DATE0 = ?)) AND ((? = 1 AND DES0 IS NULL) OR (DES0 = ?)) AND ((? = 1 AND REW0 IS NULL) OR (REW0 = ?)) AND ((? = 1 AND APP0 IS NULL) OR (APP0 = ?)) AND ((? = 1 AND REASON0 IS NULL) OR (REASON0 = ?)) AND ((? = 1 AND DATE1 IS NULL) OR (DATE1 = ?)) AND ((? = 1 AND DES1 IS NULL) OR (DES1 = ?)) AND ((? = 1 AND REW1 IS NULL) OR (REW1 = ?)) AND ((? = 1 AND APP1 IS NULL) OR (APP1 = ?)) AND ((? = 1 AND REASON1 IS NULL) OR (REASON1 = ?)) AND ((? = 1 AND DATE2 IS NULL) OR (DATE2 = ?)) AND ((? = 1 AND DES2 IS NULL) OR (DES2 = ?)) AND ((? = 1 AND REW2 IS NULL) OR (REW2 = ?)) AND ((? = 1 AND APP2 IS NULL) OR (APP2 = ?)) AND ((? = 1 AND REASON2 IS NULL) OR (REASON2 = ?)) AND ((? = 1 AND DATE3 IS NULL) OR (DATE3 = ?)) AND ((? = 1 AND DES3 IS NULL) OR (DES3 = ?)) AND ((? = 1 AND REW3 IS NULL) OR (REW3 = ?)) AND ((? = 1 AND APP3 IS NULL) OR (APP3 = ?)) AND ((? = 1 AND REASON3 IS NULL) OR (REASON3 = ?)) AND ((? = 1 AND DATE4 IS NULL) OR (DATE4 = ?)) AND ((? = 1 AND DES4 IS NULL) OR (DES4 = ?)) AND ((? = 1 AND REW4 IS NULL) OR (REW4 = ?)) AND ((? = 1 AND APP4 IS NULL) OR (APP4 = ?)) AND ((? = 1 AND REASON4 IS NULL) OR (REASON4 = ?)) AND ((? = 1 AND DATE5 IS NULL) OR (DATE5 = ?)) AND ((? = 1 AND DES5 IS NULL) OR (DES5 = ?)) AND ((? = 1 AND REW5 IS NULL) OR (REW5 = ?)) AND ((? = 1 AND APP5 IS NULL) OR (APP5 = ?)) AND ((? = 1 AND REASON5 IS NULL) OR (REASON5 = ?)) AND ((? = 1 AND DATE6 IS NULL) OR (DATE6 = ?)) AND ((? = 1 AND DES6 IS NULL) OR (DES6 = ?)) AND ((? = 1 AND REW6 IS NULL) OR (REW6 = ?)) AND ((? = 1 AND APP6 IS NULL) OR (APP6 = ?)) AND ((? = 1 AND REASON6 IS NULL) OR (REASON6 = ?)) AND ((? = 1 AND DATE7 IS NULL) OR (DATE7 = ?)) AND ((? = 1 AND DES7 IS NULL) OR (DES7 = ?)) AND ((? = 1 AND REW7 IS NULL) OR (REW7 = ?)) AND ((? = 1 AND APP7 IS NULL) OR (APP7 = ?)) AND ((? = 1 AND REASON7 IS NULL) OR (REASON7 = ?)))

Now, these are all fields in my table but what does this all mean? Googling around, I've seen others with this sort of error...

Thanks again!

 

[Tom]

This is correct what do you mean by an error; asides from not understanding it?

Think about it, the update statement obviously can not say Title1 = "My title Here" since it doesnt know the field value, yet alone the different value of every row. So instead it will play a question mark to be used as a parameter. The row column will be set as the source and when you call the update it will pass the correct values.

Not sure if I explained that one right...

 

[jsurpless]

OK... if that's what it's supposed to look like, that's fine but I get the following exception when I try to run .Update

Exception Type : System.Data.OleDb.OleDbException

Syntax error in UPDATE statement.

When I got that, I started poking around during runtime and that's when I saw the .UpdateCommand being that previously posted string... which is what led me to think it was an "error"...

Thanks again!