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]

By ensuring that your SQL looks like:

"UPDATE [table name] SET ..."

That makes sense but if I'm not explicitly setting the .UpdateCommand but am instead allowing .Update to build it automatically - how would I do it with this in mind?

 

[cjard]

Er.

Write it correctly in the SELECT?

"SELECT * FROM [table name] WHERE..."

 

[jsurpless]

Here's the code I'm using for the OleDbDataAdapter

strMSAccess_Connection_SQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFilePath
MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)
MSAccess_Connection.Open()

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

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)

It seems that I'm doing what you mentioned...

 

[cjard]

and what is the problem?

 

[jsurpless]

When using the .Update function, allowing .NET to build the SQL statement dynamically - I get an UPDATE syntax error...

 

[cjard]

And what is the error?

 

[jsurpless]

Exception Type : System.Data.OleDb.OleDbException

Syntax error in UPDATE statement.

 

[cjard]

Show the code used to create and assign the UpdateCommand, and also the generated command text

 

[jsurpless]

What I'm saying that I am not explicitly specifying the .UpdateCommand... as I understand it, .Update will automatically generate that itself (if not specified)...

I'm making the changes to the DataTable and then using .Update to apply them to the database... no assignment of .UpdateCommand

Here's the code

Dim MSAccess_DatabaseTable_Changes As DataTable

MSAccess_DatabaseTable_Changes = MSAccess_DatabaseTable.GetChanges

If Not IsNothing(MSAccess_DatabaseTable_Changes) Then

  MSAccess_Database_Builder = New OleDbCommandBuilder(MSAccess_Database_Adapter)

  MSAccess_Database_Adapter.Update(MSAccess_DatabaseTable_Changes)

End If

On a different note, I also get an 'INSERT INTO' error when using this code...

Dim New_DatabaseDrawing_Table_Row As DataRow = DatabaseDrawing_Table.NewRow()

New_DatabaseDrawing_Table_Row(c_Database.DrawingFileName_Field) = strAutoCADFile

DatabaseDrawing_Table.Rows.Add(New_DatabaseDrawing_Table_Row)

once I process it through the above code...

 

[cjard]

What I'm saying that I am not explicitly specifying the .UpdateCommand... as I understand it, .Update will automatically generate that itself (if not specified)...

Er, yes.. Though you need to tell the CommandBuilder what your quote prefix and suffix were for the table names, otherwise it generates statements like:

INSERT INTO table ...

instead of

INSERT INTO [table name] ...


See here:

        Dim oconn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\YOUR.mdb;Persist Security Info=True")

        Dim ocmd As New OleDb.OleDbCommand("CREATE TABLE [Table Name Spaces] (MyID LONG CONSTRAINT PK_MyID PRIMARY KEY, FirstName TEXT(20), LastName Text(20) )", oconn)
        ocmd.Connection.Open()
        ocmd.ExecuteNonQuery()

        Dim oda As OleDb.OleDbDataAdapter
        oda = New OleDb.OleDbDataAdapter("SELECT * FROM [Table Name Spaces]", oconn)

        Dim ocb As New OleDb.OleDbCommandBuilder(oda)
        ocb.QuotePrefix = "["
        ocb.QuoteSuffix = "]"
        'oda.InsertCommand = ocb.GetInsertCommand()
        'oda.UpdateCommand = ocb.GetUpdateCommand()
        'oda.DeleteCommand = ocb.GetDeleteCommand()

        Dim ds As New DataSet
        oda.Fill(ds)

        ds.Tables(0).Rows.Add(1, "John", "Smith")

        oda.Update(ds)

        ds.Tables(0).Rows(0)("FirstName") = "Jane"

        oda.Update(ds)

        ds.Tables(0).Rows(0).Delete()

        oda.Update(ds)

Note if you dont set the InsertCommand etc, then DataAdapter will get one each time from the command builder but never assign it internally; it can be awfully confusing to look at a DA and see no insert command, yet still find it generating error messages like there is one

If you set them yourself, you'd can see the generated SQLs; this probably would have made it more apparent that there were syntax errors.

Most important note is that you need to set the QuotePrefix and QuoteSuffix propeties of the command builder

 

[jsurpless]

OK, that QuotePrefix and Suffix seem to have done the trick...

I understand your recommendation about setting the .UpdateCommand, etc but when it just looks like this

UPDATE [Table1] SET [TITLE_2] = ?, etc, etc

It doesn't seem to mean anything so why bother?

BTW, thanks so much for your continued help... it's been much appreciated...

 

[jsurpless]

Another quick question - before starting this whole process, I'd like to verify that the datatable does, in fact, have a primary key...

How would I do this?

Thanks again!

 

[cjard]

OK, that QuotePrefix and Suffix seem to have done the trick...

I understand your recommendation about setting the .UpdateCommand, etc but when it just looks like this

UPDATE [Table1] SET [TITLE_2] = ?, etc, etc

It doesn't seem to mean anything so why bother?

BTW, thanks so much for your continued help... it's been much appreciated...

Not quite sure I understand the question, and I havent read the code written by Microsoft on how the DataAdapter works but I suspect that if there is an If UpdateCommand Is Nothing Then commandBuilder.GetUpdateCommand()

And maybe commandbuilder caches the last update command it built so to save the CPU cycles.. Or maybe not.. However, if we set the UC, then we can be sure it wont be generated many times needlessly.

 

[cjard]

Another quick question - before starting this whole process, I'd like to verify that the datatable does, in fact, have a primary key...

How would I do this?

Thanks again!

You can probably just catch the exception from the commandbuilder; it will error out if there is no PK..

Alternately, GetSchema may give you the info; google it