oledb problem

[zdenko]

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..
it is nice
'cept...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

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?

 

[cjard]

ADO.NET: Optimistic Concurrency Updating Logic section 2)

Read the DW3 link in my signature, section Creating a Simple Data App - tutorial on how to do data access properly