Syntax Error in INSERT INTO command..please help!!!

[Sam]

I'm having a bit of problems entering a new row to an
access database and what I would like to know is do I
have to assign certain values in my INSERT INTO SQL
statement. I'm using an access database locally on my
computer.
I have read about the commandbuilder and it said that the
insert command is automatically generated. Below is my
code. Can someone help me out into where the syntax error
is happening:

Dim readwrite As OleDb.OleDbDataAdapter
Dim CmdBuilder As OleDb.OleDbCommandBuilder
Dim ds As DataSet = New DataSet()
Dim table As DataTable
Dim row As DataRow

'sqlstring is global variable with string "Select
'* from inv_compt_invest"
readwrite = New OleDb.OleDbDataAdapter(sqlstring,
dbInventaire)
CmdBuilder = New OleDb.OleDbCommandBuilder
(readwrite)
readwrite.InsertCommand =
CmdBuilder.GetInsertCommand()
MsgBox(readwrite.InsertCommand.CommandText)
Try
readwrite.Fill(ds, "inv_compt_invest")

'output data from dataset
table = ds.Tables.Item(0)
If table.Rows.Count <> 0 Then

'Enter new records
If newdataentry = True Then
row = table.NewRow()
row("Date d'acquisition") = CDate
(TextBox2.Text)
row("Num de Compte") = CInt
(TextBox3.Text)
row("Designation de l'article") =
TextBox4.Text
row("Quantite") = CInt(TextBox5.Text)
row("Fournisseur") = TextBox6.Text
row("Num de la facture") =
TextBox7.Text
row("Marque") = TextBox8.Text
row("Localisation") = TextBox9.Text
table.Rows.Add(row)

'Update the database table
readwrite.Update
(ds, "inv_compt_invest")
'The exception gets triggered here through debugging
newdataentry = False
Else
MsgBox("Need to enter new data to save
to Database")
End If
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try

thanks,

Sam

 

[William Ryan]

Sam:

Try wrapping the Update Command in its own try catch block and see what
Exception.ToSTring (instead of .Messsage) is. ALso, with your
commandbuilder, all you need to do is reference the DataAdapter to it....you
don't need to reference the insert command.

This code for instance allows for an update/insert/delete from anywhere on
the form...
Dim cn As New SqlConnection

cn = New
SqlConnection(ConfigurationSettings.AppSettings.Item("connectString").ToStri
ng)

da = New SqlClient.SqlDataAdapter("Select * from tbl_Physicians", cn)

Dim sqlCmdBuilder As SqlClient.SqlCommandBuilder

sqlCmdBuilder = New SqlClient.SqlCommandBuilder(da)

Try

da.Fill(ds, "Physicians")

Catch ex As Exception

End Try

With dg

..AllowSorting = True

..AlternatingBackColor = System.Drawing.Color.Bisque

..SetDataBinding(ds, "Physicians")

End With

 

[Marina]

What are the column names in your table?

Often, the issue involves having a column name that happens to be a reserved
SQL keyword.

 

[David Sceppa]

Marina is correct in pointing out that use of a reserved
word for a column name is the common cause of this error.
Assuming that's the case, setting CommandBuilder.QuotePrefix to
"[" and CommandBuilder.QuoteSuffix to "]" should resolve the
problem.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Sam]

Hi Marina,

The column names in my table are:

Numero d'Inventaire
Date d'acquisition
Num de Compte (which is the primary key in my table)
Designation de l'article
Quantite
Fournisseur
Num de la facture
Marque
Localisation

I don't think that there are any reserved SQL words here.
When I look at the message box for the insertcommand I
get "INSERT INTO inv_compt_invest(Date d'acquisition, Num
de Compte, Designation de l'article, Quantite,
Fournisseur, Num de la facture, Marque, Localisation )
VALUES(?,?,?,?,?,?,?,?)" Do I have to do something to
fill in the question marks? Is that what could be causing
the syntax error?

Sam

 

[Sam]

I added those two lines of code and "Voila!!" it made a
huge difference, now the row is entered successfully. I
guess it was one of the column names. Thanks for all the
help.

Sam

 

[Anthony Williams]

Sam,

It would appear that the problem is not reserved words, but most likely the
spaces in your column names )

But quoting them using hard brackets, as you've seen, will work fine with
spaced names.

Cheers,
Anthony

 

[Paul Clement]

¤ Hi Marina,
¤
¤ The column names in my table are:
¤
¤ Numero d'Inventaire
¤ Date d'acquisition
¤ Num de Compte (which is the primary key in my table)
¤ Designation de l'article
¤ Quantite
¤ Fournisseur
¤ Num de la facture
¤ Marque
¤ Localisation
¤
¤ I don't think that there are any reserved SQL words here.
¤ When I look at the message box for the insertcommand I
¤ get "INSERT INTO inv_compt_invest(Date d'acquisition, Num
¤ de Compte, Designation de l'article, Quantite,
¤ Fournisseur, Num de la facture, Marque, Localisation )
¤ VALUES(?,?,?,?,?,?,?,?)" Do I have to do something to
¤ fill in the question marks? Is that what could be causing
¤ the syntax error?
¤

You bet. Are you adding parameters to the Parameter collection of your Insert Command? It looks like
you need eight of them.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)