Simple dataset update @ runtime not working

2 Nov 2006 7:44 AM

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?

2 Nov 2006 9:29 AM

gene kelley

Show quote Hide quote

On 1 Nov 2006 23:44:05 -0800, "Bryan" <bryanv***@gmail.com> wrote:

>        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

2 Nov 2006 8:50 PM

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!

3 Nov 2006 8:21 AM

gene kelley

Show quote Hide quote

On 2 Nov 2006 12:50:27 -0800, "Bryan" <bryanv***@gmail.com> wrote:

>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