OleDB problem - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

OleDB problem

zax_14zax_14 Posts: 4Member
[code]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 + "Datadata.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[/code]

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:
[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 + "Datadata.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")[/code]


seems to have a problem specifically with the update command, not with the insert or delete one, tho this fact wasnt tested...

EDIT:
PS - the problem is only with the update statement since i can read the data without problems

EDIT2:
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
Sign In or Register to comment.