By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,041 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,041 IT Pros & Developers. It's quick & easy.

Syntax error in INSERT INTO statement

P: n/a
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()
Nov 18 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <Ig************@juno.comwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
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()


Nov 18 '06 #2

P: n/a
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 [MVP]" <vb***@bellsouth.netwrote in message
news:eA**************@TK2MSFTNGP02.phx.gbl...
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" <Ig************@juno.comwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
>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()



Nov 18 '06 #3

P: n/a
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" <Ig************@juno.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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 [MVP]" <vb***@bellsouth.netwrote in message
news:eA**************@TK2MSFTNGP02.phx.gbl...
>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" <Ig************@juno.comwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
>>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()




Nov 18 '06 #4

P: n/a
Warex,

Are you aware that you use some not needed code.
(inline everytime under your code)
>
Dim cns As String = ""
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
I don't see the sence for this one
DataSets.AcceptChanges()
This one is included in the DataAdapter Update, only if you use partial
updates you need it.

Cor
Nov 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.