Simple dataset update @ runtime not working

[Bryan]

I have an MS Access DB with a table' Isometric' . One of the columns
is named 'Sheet'. I am doin a test to see if I can change a value in
the dataset and update the Access table with the DataAdapter.Update
method. It is not working:

Dim con As System.Data.OleDb.OleDbConnection = DAL.GetOLECon
Dim dap As New System.Data.OleDb.OleDbDataAdapter("SELECT *
FROM Isometric", con)

Dim cmb As New System.Data.OleDb.OleDbCommandBuilder(dap)
Dim das As New DataSet

das.Tables(0).Rows(0).Item("Sheet") = 999

dap.Update(das, "Isometric")

Here is the error I get

Syntax error (missing operator) in query expression '((ID_Isometric =
?) AND ((? = 1 AND Line number IS NULL) OR (Line number = ?)) AND ((? =
1 AND Sheet IS NULL) OR (Sheet = ?)) AND ((? = 1 AND Spec IS NULL) OR
(Spec = ?)) AND ((? = 1 AND Material delivered IS NULL) OR (Material
delivered = ?)) AND ((? = 1'.

is the commandbuilder just creating the update commandtext inccorectly?

 

[gene kelley]

Dim con As System.Data.OleDb.OleDbConnection = DAL.GetOLECon
Dim dap As New System.Data.OleDb.OleDbDataAdapter("SELECT *
FROM Isometric", con)

Dim cmb As New System.Data.OleDb.OleDbCommandBuilder(dap)
Dim das As New DataSet

das.Tables(0).Rows(0).Item("Sheet") = 999

dap.Update(das, "Isometric")

You've created the connection object - is the connection open?
cmb OuotePrefix and QuoteSuffix?
I don't see where you have filled the adapter (dap)

Dim con As System.Data.OleDb.OleDbConnection = DAL.GetOLECon

Dim dap As New System.Data.OleDb.OleDbDataAdapter _
("SELECT * FROM Isometric", con)
Dim cmb As New System.Data.OleDb.OleDbCommandBuilder(dap)
cmb.QuotePrefix = "["
cmb.QuoteSuffix = "]"

Dim das As DataSet = New DataSet
If con.State = ConnectionState.Closed Then
con.Open()
End If
dap.Fill(das, "Isometric")
If das.Tables(0).Rows.Count > 0 Then
das.Tables(0).Rows(0).Item("Sheet") = 999
dap.Update(das, "Isometric")
End If


Gene

 

[Bryan]

It was the QuotePrefix/Sufix. I am updating tables that users create,
so there is always the possibility of spaces & other bad characters in
column names.

I have been reading article after article, post after post about the
drawbacks of the ADO commandbuilder object, one of them being that it
can't work if your column names have spaces/bad characters in them. I
agree with most the criticism, but I am amazed that I havn't read about
the Quoteprefix/suffix thing before. As long as you know what your
back end uses to enclose column names in SQL, this is no longer a
problem. For the simple updating I was trying to do, this problem has
now gone away.
After reading your post I searched for articles that talk about the
Quoteprefix/suffix members and there didn't seem to be many relevant
ones that recognized the value of these members.
For simple dataset updates, in certain situations such as mine, the
quoteprefix/suffix members have made the commandbuilder object usefull
and will save me time.

Thanks for the help!

 

[gene kelley]

It was the QuotePrefix/Sufix. I am updating tables that users create,
so there is always the possibility of spaces & other bad characters in
column names.

I have been reading article after article, post after post about the
drawbacks of the ADO commandbuilder object, one of them being that it
can't work if your column names have spaces/bad characters in them. I
agree with most the criticism, but I am amazed that I havn't read about
the Quoteprefix/suffix thing before. As long as you know what your
back end uses to enclose column names in SQL, this is no longer a
problem. For the simple updating I was trying to do, this problem has
now gone away.
After reading your post I searched for articles that talk about the
Quoteprefix/suffix members and there didn't seem to be many relevant
ones that recognized the value of these members.
For simple dataset updates, in certain situations such as mine, the
quoteprefix/suffix members have made the commandbuilder object usefull
and will save me time.

Thanks for the help!

The example I gave was based on an example found in the Help File which includes the
quote prefix/sufix. I was intially using this sort of method, myself. But for some
reason (at least here), using the command builder and data adapter seems to have a
proportional performance hit the larger the data base. I have since modified my
project to use explicit UPDATE, INSERT, or DELETE commmands with Execute.NonQuery()
which gives the same performace regardless of the database size.

Gene