oledb Command builder generating gibberish UPDATE command

[zdenko]

Ive posted this before so I'm gonna copy-paste this more-less to see if anyone can figure it out...

Public Shared Sub set_week(ByVal p As Integer, ByVal week As Integer)
        If p = 1 Then
            p = 3
        Else
            p = 4
        End If

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " + Application.StartupPath + "\Data\data.mdb"
        Dim da As OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        con.Open()

        sql = "SELECT * FROM settings"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        con.Close()

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables("AddressBook").Rows(0).Item(p) = week

        Try
            da.Update(ds, "AddressBook")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

this is my code...
and it doesnt work for some unexplainable reason...
if i use the "AddressBook" bit in the update command, it throws a syntax error (missing operator)
if i dont, throws the "unable to find tablemappings['table']" one...

the thing is, i used it dozens of times and it always worked...

ive been searching everywhere for the solution with no avail...

this is an example that WORKS (and yes i did in fact delete the row and made a new one...no particular reason)..a button code:

Main.ColorDialog1.Color = Me.BackColor
        Main.ColorDialog1.ShowDialog()
        Dim mycolor As Color = Main.ColorDialog1.Color
        Dim A As Byte = mycolor.A 'alpha 
        Dim R As Byte = mycolor.R 'red 
        Dim G As Byte = mycolor.G 'green 
        Dim B As Byte = mycolor.B 'blue 
        
        Me.BackColor = Color.FromArgb(255, R, G, B)
        Main.BackColor = Color.FromArgb(255, R, G, B)
        Main.TrackBar1.BackColor = Color.FromArgb(255, R, G, B)
        Main.tab_p.BackColor = Color.FromArgb(255, R, G, B)
        Main.tab_u.BackColor = Color.FromArgb(255, R, G, B)

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " + Application.StartupPath + "\Data\data.mdb"
        Dim da As OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        con.Open()

        sql = "SELECT * FROM colors"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        con.Close()


        ds.Tables("AddressBook").Rows(0).Delete()
        Dim row As DataRow = ds.Tables("AddressBook").NewRow
        row.Item(0) = R
        row.Item(1) = G
        row.Item(2) = B
        ds.Tables("AddressBook").Rows.Add(row)

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        da.Update(ds, "AddressBook")

i found the trigger but not the cause...the update command the command builder is generating is:
UPDATE settings SET backcolor = ?, text_color = ?, two_sch = ?, m_week_1 = ?, m_week_2 = ?, year = ?, 1st_start = ? WHERE ((backcolor = ?) AND ((? = 1 AND text_color IS NULL) OR (text_color = ?)) AND ((? = 1 AND two_sch IS NULL) OR (two_sch = ?)) AND ((? = 1 AND m_week_1 IS NULL) OR (m_week_1 = ?)) AND ((? = 1 AND m_week_2 IS NULL) OR (m_week_2 = ?)) AND ((? = 1 AND year IS NULL) OR (year = ?)) AND ((? = 1 AND 1st_start IS NULL) OR (1st_start = ?)))

i have no idea why it does that o.o

do i have to set each column value manually? isn't it natural, since i didn't change it, it stays the same?

does anybody have any idea of what it is? or at least, does anyone know of a simpler or alternative code i can use to access MSAccess databases?

 

[jmcilhinney]

It's not gibberish. It's optimistic concurrency. What code does is ensure that the record is not saved if the data in the database is not the same as what you originally retrieved. That is so that you don't overwrite another user's changes without even know that they were saved. If you don't need that, i.e. it's a single user database, then I think that setting the ConflictOption property of the CommandBuilder to OverwriteChanges should simplify the WHERE clause down to using just the PK.

As for the syntax error, that's usually due to a column name being a reserved word or containing special characters, usually spaces. You should avoid using such column names in the first place if you can. If it can't be avoided, set the QuotePrefix and QuoteSuffix of the CommandBuilder to "[" and "]" respectively.

 

[zdenko]

oh thank you so much! the conflictOption did the trick! what i dont understand still is why it throws that error just there...but nevermind...i'll add cb.conflictoption = conflictoption.overwritechanges to every db update in the program to avoid future errors.

and i made sure there aren't any reserved words as column names or such.

Thank you again... it feels good to finally have this solved after over 4 months

 

[jmcilhinney]

If you don't set the ConflictOption but you do set the QuotePrefix and QuoteSuffix, does it work then?