System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'

Question

I am trying to create a create button in visual basic so the record goes into my database but it get this error:

System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.

my code is here and i get the error on da.Update(ds, "Test")

Dim cb As New OleDb.OleDbCommandBuilder(da)

Dim dsNewRow As DataRow

dsNewRow = ds.Tables("Test").NewRow()

dsNewRow.Item("Username") = txtUsername.Text
dsNewRow.Item("Password") = txtPassword.Text
dsNewRow.Item("Email Address") = txtEmailAddress.Text

ds.Tables("Test").Rows.Add(dsNewRow)

da.Update(ds, "Test")

any help would be amazing.

According to the error, It is related to your sql text. So I hope you can provide it with me so that I can do some test. — Jack J Jun
– Jack J Jun, Commented Dec 11, 2019 at 2:41

jmcilhinney · Accepted Answer · 2019-12-11 01:00:39Z

When this error occurs with a command builder, it is generally because one or more of your column names is a reserved word or contains spaces or other special characters. In your case, you have what is likely a reserved word, i.e. Password, and also a space in Email Address.

The first choice option should be to avoid such column names. In this case, Email Address should be EmailAddress, just as you would use for an identifier in VB. There's not necessarily an obvious alternative for Password, except that passwords should pretty much never be stored in the clear, so a real app should hash passwords and then the column could be named PasswordHash.

Failing that, you can set the QuotePrefix and QuoteSuffix properties of your command builder so that your column names will be escaped. In your case, "[" and "]" the the values you probably ought to be using, i.e.

cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

Collectives™ on Stack Overflow

System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related