Syntax error in INSERT INTO statement

  • Thread starter Thread starter Warex
  • Start date Start date
W

Warex

Maybe someone can help, my code works untill it gets to update when it tells
me Syntax error in INSERT INTO statement.
Thought this was automaticaly created according to MS.
Im lost, my code is below to copy and paste. Just change to your database,
tablename, fields to edit and sql.

AGGHHHHHH

thanks
Public Sub insertit(ByRef Msql As String)

Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection

Dim cns As String = ""

Dim mDBN As String = "C:\BNM.MDB"

Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mDBN
& ";Persist Security Info = False"

Connection.ConnectionString = cCON

Connection.Open()

Dim DAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(Msql,
Connection)

Dim Cmand As New OleDb.OleDbCommand

Dim CBuild As New OleDb.OleDbCommandBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill(DataSets, "Numbers")

CBuild = New OleDb.OleDbCommandBuilder(DAdapter)

'create pkey

With DataSets.Tables("Numbers")

..PrimaryKey = New DataColumn() {.Columns("Number")}

End With

'create a select command with all parameters

DAdapter.InsertCommand = CBuild.GetInsertCommand()

Dataroww = DataSets.Tables("Numbers").NewRow

Dataroww.BeginEdit()

Dataroww("Number") = "9999999"

Dataroww("Name") = "KJHGFDSA"

Dataroww("Block") = True

Dataroww.EndEdit()

DataSets.Tables("Numbers").Rows.Add(Dataroww)

DAdapter.Update(DataSets, "Numbers") ' errors out here insert error

DataSets.AcceptChanges()
 
Hi,

Usually what causes this is one of the column names is a
keyword. To prevent the error you need to place the column name in [ ].

Instead of using a query like
Select * from MyTable

try using

Select [col1], [col2], [col3] from MyTable

The commandbuilder will use the col name is [ ] so you will not get the
error.

Ken
 
AGGHHHHHHHHHH
WHY did the MicroSoft Example that I printed at 40 pages DID NOT say that?
AGGGGGGGGGGGHHHHHHHHHHH

Thanks.......

Hitting myself, hitting myself, hitting myself...............

Ken Tucker said:
Hi,

Usually what causes this is one of the column names is a
keyword. To prevent the error you need to place the column name in [ ].

Instead of using a query like
Select * from MyTable

try using

Select [col1], [col2], [col3] from MyTable

The commandbuilder will use the col name is [ ] so you will not get the
error.

Ken
---------------------------
Warex said:
Maybe someone can help, my code works untill it gets to update when it
tells me Syntax error in INSERT INTO statement.
Thought this was automaticaly created according to MS.
Im lost, my code is below to copy and paste. Just change to your
database, tablename, fields to edit and sql.

AGGHHHHHH

thanks
Public Sub insertit(ByRef Msql As String)

Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection

Dim cns As String = ""

Dim mDBN As String = "C:\BNM.MDB"

Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
mDBN & ";Persist Security Info = False"

Connection.ConnectionString = cCON

Connection.Open()

Dim DAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(Msql,
Connection)

Dim Cmand As New OleDb.OleDbCommand

Dim CBuild As New OleDb.OleDbCommandBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill(DataSets, "Numbers")

CBuild = New OleDb.OleDbCommandBuilder(DAdapter)

'create pkey

With DataSets.Tables("Numbers")

.PrimaryKey = New DataColumn() {.Columns("Number")}

End With

'create a select command with all parameters

DAdapter.InsertCommand = CBuild.GetInsertCommand()

Dataroww = DataSets.Tables("Numbers").NewRow

Dataroww.BeginEdit()

Dataroww("Number") = "9999999"

Dataroww("Name") = "KJHGFDSA"

Dataroww("Block") = True

Dataroww.EndEdit()

DataSets.Tables("Numbers").Rows.Add(Dataroww)

DAdapter.Update(DataSets, "Numbers") ' errors out here insert error

DataSets.AcceptChanges()
Click to expand...
Click to expand...
 
I'm sure Microsoft took it for granted, as most of us do.
I've gotten a little paranoid about it, myself.

Stop hitting yourself, you're going to cause a brain
tumor, and then you'll never get your work done. ;-)

Robin S.
------------------------------

Warex said:
AGGHHHHHHHHHH
WHY did the MicroSoft Example that I printed at 40 pages DID NOT say that?
AGGGGGGGGGGGHHHHHHHHHHH

Thanks.......

Hitting myself, hitting myself, hitting myself...............

Ken Tucker said:
Hi,

Usually what causes this is one of the column names is a
keyword. To prevent the error you need to place the column name in [ ].

Instead of using a query like
Select * from MyTable

try using

Select [col1], [col2], [col3] from MyTable

The commandbuilder will use the col name is [ ] so you will not get the
error.

Ken
---------------------------
Warex said:
Maybe someone can help, my code works untill it gets to update when it
tells me Syntax error in INSERT INTO statement.
Thought this was automaticaly created according to MS.
Im lost, my code is below to copy and paste. Just change to your
database, tablename, fields to edit and sql.

AGGHHHHHH

thanks
Public Sub insertit(ByRef Msql As String)

Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection

Dim cns As String = ""

Dim mDBN As String = "C:\BNM.MDB"

Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
mDBN & ";Persist Security Info = False"

Connection.ConnectionString = cCON

Connection.Open()

Dim DAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(Msql, Connection)

Dim Cmand As New OleDb.OleDbCommand

Dim CBuild As New OleDb.OleDbCommandBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill(DataSets, "Numbers")

CBuild = New OleDb.OleDbCommandBuilder(DAdapter)

'create pkey

With DataSets.Tables("Numbers")

.PrimaryKey = New DataColumn() {.Columns("Number")}

End With

'create a select command with all parameters

DAdapter.InsertCommand = CBuild.GetInsertCommand()

Dataroww = DataSets.Tables("Numbers").NewRow

Dataroww.BeginEdit()

Dataroww("Number") = "9999999"

Dataroww("Name") = "KJHGFDSA"

Dataroww("Block") = True

Dataroww.EndEdit()

DataSets.Tables("Numbers").Rows.Add(Dataroww)

DAdapter.Update(DataSets, "Numbers") ' errors out here insert error

DataSets.AcceptChanges()
Click to expand...
Click to expand...
Click to expand...
 
Warex,

Are you aware that you use some not needed code.
(inline everytime under your code)
Dim cns As String = ""
Click to expand...
the = "" is not needed in VB.Net
However you don't use that string
'create pkey
With DataSets.Tables("Numbers")
.PrimaryKey = New DataColumn() {.Columns("Number")}
End With
Click to expand...
I don't see the sence for this one
DataSets.AcceptChanges()
Click to expand...
This one is included in the DataAdapter Update, only if you use partial
updates you need it.

Cor
 
Back
Top