Syntax error with Access database

[DR Gorden]

I fill a dataset (dsGRAD) using the Jet 4.0 provider and the
OleDbDataAdapter with data from an Access database table containing 19
columns. Each column is bound to a textbox on a form. Changes can be
made to the dataset text boxes with no problems but I get a syntax
error attempting to update or insert into a Access database. I have not
yet attempted a Delete operation.

The code I am using follows:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Try
daGRAD.Update(dsGRAD, "GRADUATE") (error on this line)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
bLoading = True
dsGRAD.GRADUATE.Clear()
daGRAD.Fill(dsGRAD, "GRADUATE")
bLoading = False
txtClass.Focus()
End Sub

I am using the generated insert, delete, update commands provided when
the data adapter is generated.

The syntax error generated is identical to the error message mentioned
in the message from Dustin Wilson dated 12 Aug 2005 to this group with
the exception of the different names and locations of the database.

Words of wisdom would be greatly appreciated
Thanks
Don G

 

[W.G. Ryan MVP]

Since you are using generated commands - it's probalby not a problem w/ not
paramaterizing your queries (which if you don't, will blow up with names
like O'Toole b/c of the apostrophe). ANyway, the next cuprit is probably a
Reserved word (http://www.knowdotnet.com/articles/reservedwords.html) Check
against the list of reserved words (the article above has them) . If you
have a reserved word - you have two choices, one good, one bad. The good
choice is to change the column name to a conforming one. I've heard
countless people say that it's too late in development to change column
names, but invariably, they or someone else forgets about this some point
down the road and has to waste a bunch of time trying to debug the problem.

The other choice is to wrap the column name in braces liek this
[ColumnName] instead of ColumnName - this will should addres the immediate
problem - but if this is the problem please know that it's just a bandaid
and you'll probably want to change the root cause.

If it's none of the above, let me know and I'llsee what I can come up with.

Cheers,

Bill

 

[Paul Clement]

¤ I fill a dataset (dsGRAD) using the Jet 4.0 provider and the
¤ OleDbDataAdapter with data from an Access database table containing 19
¤ columns. Each column is bound to a textbox on a form. Changes can be
¤ made to the dataset text boxes with no problems but I get a syntax
¤ error attempting to update or insert into a Access database. I have not
¤ yet attempted a Delete operation.
¤
¤ The code I am using follows:
¤
¤ Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles btnUpdate.Click
¤
¤ Try
¤ daGRAD.Update(dsGRAD, "GRADUATE") (error on this line)
¤ Catch ex As Exception
¤ MessageBox.Show(ex.ToString)
¤ End Try
¤ bLoading = True
¤ dsGRAD.GRADUATE.Clear()
¤ daGRAD.Fill(dsGRAD, "GRADUATE")
¤ bLoading = False
¤ txtClass.Focus()
¤ End Sub
¤
¤ I am using the generated insert, delete, update commands provided when
¤ the data adapter is generated.
¤
¤ The syntax error generated is identical to the error message mentioned
¤ in the message from Dustin Wilson dated 12 Aug 2005 to this group with
¤ the exception of the different names and locations of the database.
¤
¤ Words of wisdom would be greatly appreciated

What is the value of your InsertCommand?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Mark Ashton]

Have you tried setting the QuotePrefix and QuoteSuffix on the command
builder?

The OleDbCommandBuilder does not have default values for the QuotePrefix &
QuoteSuffix properties.
Dim adapter as new OleDbDataAdapter
Dim builder as new OleDbCommandBuilder
builder.QuotePrefix = "`" ' access uses backwards single quote,
sqlserver uses left square bracket "["
builder.QuoteSuffix = "'" ' access uses normal single quote, sqlserver
uses right square bracket "]"
builder.Adapter = adapter

 

[DR Gorden]

Access database had 3 of the 19 columns named CLASS, FIRST, LAST.

Column names changed to G_CLASS, F_NAME, L_NAME and problem was solved.
All commands now work as they should.

Thanks for the assistance & consideration received

Don G