Syntax Error in INSERT statement?

[savedlema]

Hi friends!
I have a piece of code that I think should be working just fine, but I wonder why it doesn't work, and I get an "Syntax Error in INSERT statement" error message.
Here is the code to affect a table called "Users" in my Access database.

            Dim cmd As OleDb.OleDbCommand
            Dim sSQL As String

            con.Open()
            sSQL = "INSERT INTO Users (Username, Password) " _
                   + "VALUES ('" & Trim(Me.txtUser1.Text) & "', '" & Trim(Me.txtPass1.Text) & "') "

            cmd = New OleDb.OleDbCommand
            With cmd
                .CommandType = CommandType.Text
                .CommandText = sSQL
                .Connection = con
            End With
            cmd.ExecuteNonQuery()

            MsgBox("information updated succesfully", vbInformation)
            con.Close()

Thanks all.

[Reverend Jim]

It is possible that either txtUser1.Text or txtPass1.Text contains a character that is screwing up the statement. For example, if the username is "O'Neal" and the password is "froggie" then the resulting insert statement is

INSERT INTO Users (Username, Password) VALUES ('O'Neal','froggie')

build the query in a string and display it before executing it. If the problem isn't obvious then post the query string back here.

/EDIT

This is what happens when I get interrupted in the middle of an answer. Obviously you are already building the query in a variable. But displaying the contents will help clear up the problem.

[jireh]

The best way to check is...

after this line of code...

sSQL = "INSERT INTO Users (Username, Password) " _
                       + "VALUES ('" & Trim(Me.txtUser1.Text) & "', '" & Trim(Me.txtPass1.Text) & "') "

Check/Get the value of sSQL and run it in the sql server.

HTH

[savedlema]

Reverend Jim, thank you for that alert. But, its not the case with me. The data I tried to test entering are Frank for username and shalom for password. Anything else? But,Thank you.

Jireh, thank you for your response. But I don't use an SQL server, I use Access database.

I'm still trying.

[Reverend Jim]

I created an Access database with the table

users
    username (Text primary key)
    password (Text)

Then I executed the following query

INSERT INTO users (username, password) VALUES ('Frank','shalom')

It inserted with no errors. Perhaps the problem is with your database or the connection string.

[savedlema]

Reverend Jim,
Thank you so much for your continued support. I have one thing to my amazement.You know I tried so many codes for the same INSERT but it never worked. But, I went to my database, changed the name for the password field to anything like "SecretWord" and it works!. There is no problem with "Username", but if I rename it to password again, there is an INSERT Syntax error. Change it back to "SecretWord" and it works just fine. Now, I don't know why and I wonder if anyone can reason this.
I'm okay now. Thanks, and many thanks to the Dani Web community.
Shalom!

[Reverend Jim]

That is something I always seem to overlook. Even though "password" is a valid field name in my version of Access (there were no complaints), in some databases "password" is a reserved word. In that case, in queries you have to enclose the field name in square brackets as in

    INSERT INTO users (username, [password]) VALUES ('Frank','shalom')

My apologies. I should have spotted that right away.

[savedlema]

I didn't know that, but I have learnt my lesson in a hard way! Well, at least now I know!
And, what are other reserved keywords? It'll be good to know them.

Again, Thanks Reverend for you continued support here.
Shalom!