who is wrong me or ADO ? {Sorted }

[Pirate]

I am using this code to save info written in textboxes to db .Everything seem to be fine but I don't know why it keeps throwing error saying "error in insert syntax "
the error happens while reaching update line.
Help plz

Private Sub Button1_Click(ByVal sender As System.Object, _ 
 ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim MySQLStr As String = "Select * From MyTab"
        Dim Mypath As String = Application.StartupPath & "\webdb.mdb"
        Dim MyCon As New OleDbConnection _ 
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Mypath)
        Dim myadap As New OleDb.OleDbDataAdapter(MySQLStr, MyCon)
        Dim objCmdBld As New OleDb.OleDbCommandBuilder(myadap)
        Dim ds As DataSet = New DataSet()
        MyCon.Open()
 
        myadap.Fill(ds, "mytab")
        Dim MyDataRow As DataRow = ds.Tables("mytab").NewRow
 
        MyDataRow("URL") = Trim(TextBox1.Text)
        MyDataRow("Category") = Trim(TextBox2.Text)
        MyDataRow("Language") = Trim(TextBox3.Text)
        MyDataRow("UserName") = Trim(TextBox4.Text)
        MyDataRow("Password") = Trim(TextBox5.Text)
        MyDataRow("Comment") = Trim(TextBox6.Text)
        MyDataRow("last") = Trim(TextBox7.Text)
 
        ds.Tables("MyTab").Rows.Add(MyDataRow)
        Try
            myadap.Update(ds, "mytab")
        Catch x As Exception
            MsgBox(x.Message)
        End Try
        MyCon.Close()
    End Sub

[Pirate]

there is one table called "MyTab" and 7 columns all in XP access db.

[briancps]

I've experienced similar problems with what access thinks are 'reserved words' on an Insert statement. Try prefixing the fields in your database with say f_ and see if the error goes away. I'm thinking it might not like words like field names like password etc

regards

BH

[Psyrus]

brian is right password is a reserved word. Any column named after a reserved or keyword will throw an error.

http://msdn.microsoft.com/library/de...ra-rz_9oj7.asp

http://msdn.microsoft.com/library/de...gappendixd.asp

http://msdn.microsoft.com/library/de...imitations.asp

http://msdn.microsoft.com/library/de...d_keywords.asp

[Pirate]

I tried every way to get it to work but it seems as you said guys.
thanx a lot , and you Psyrus for the links .

[Pirate]

exactly what you said is true . I am sure this bug or whatever wasn't exist in the old ADO (vb6). nobody understands M$.

[briancps]

You are right. The problem dosn't appear to exist in ADO - it even works in Datareaders - it appears it only rears its ugly head when you are trying to INSERT or UPDATE thru disconnected Datasets. I've prefixed every field with the table name - eg Name is now EM_Name if an Employer table.

Found this was the only way to solve the problem.

regards
Brian

[Edneeis]

When a field is the same as a keyword or has a space or funky character sometimes it works if you enclose it in [].

[Password]

[Pirate]

Sometimes ??

[Edneeis]

Yes it depends what the actual error is. If it is just not recognizing the field name then that usually works.

[Pirate]

What I am asking now is If I enclosed the field name with this brackets [ ] I shouldn't get any error .? right ?

[Edneeis]

Well I guess what I'm saying is you shouldn't BUT I haven't tested it enough to say for sure.

[Pirate]

never mind . for sure I would change every reserved keyword I used in my proj to something a little bit different . thanx for care