Syntax error in INSERT INTO statement

[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.OleDbConn ection = New OleDb.OleDbConn ection

Dim cns As String = ""

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

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

Connection.Conn ectionString = cCON

Connection.Open ()

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

Dim Cmand As New OleDb.OleDbComm and

Dim CBuild As New OleDb.OleDbComm andBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill(D ataSets, "Numbers")

CBuild = New OleDb.OleDbComm andBuilder(DAda pter)

'create pkey

With DataSets.Tables ("Numbers")

..PrimaryKey = New DataColumn() {.Columns("Numb er")}

End With

'create a select command with all parameters

DAdapter.Insert Command = CBuild.GetInser tCommand()

Dataroww = DataSets.Tables ("Numbers").New Row

Dataroww.BeginE dit()

Dataroww("Numbe r") = "9999999"

Dataroww("Name" ) = "KJHGFDSA"

Dataroww("Block ") = True

Dataroww.EndEdi t()

DataSets.Tables ("Numbers").Row s.Add(Dataroww)

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

DataSets.Accept Changes()

[Ken Tucker [MVP]]

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******** ********@TK2MSF TNGP02.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.OleDbConn ection = New OleDb.OleDbConn ection

Dim cns As String = ""

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

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

Connection.Conn ectionString = cCON

Connection.Open ()

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

Dim Cmand As New OleDb.OleDbComm and

Dim CBuild As New OleDb.OleDbComm andBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill(D ataSets, "Numbers")

CBuild = New OleDb.OleDbComm andBuilder(DAda pter)

'create pkey

With DataSets.Tables ("Numbers")

.PrimaryKey = New DataColumn() {.Columns("Numb er")}

End With

'create a select command with all parameters

DAdapter.Insert Command = CBuild.GetInser tCommand()

Dataroww = DataSets.Tables ("Numbers").New Row

Dataroww.BeginE dit()

Dataroww("Numbe r") = "9999999"

Dataroww("Name" ) = "KJHGFDSA"

Dataroww("Block ") = True

Dataroww.EndEdi t()

DataSets.Tables ("Numbers").Row s.Add(Dataroww)

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

DataSets.Accept Changes()

[Warex]

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

Thanks.......

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

"Ken Tucker [MVP]" <vb***@bellsout h.netwrote in message
news:eA******** ******@TK2MSFTN GP02.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******** ********@TK2MSF TNGP02.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.OleDbConn ection = New OleDb.OleDbConn ection

Dim cns As String = ""

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

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

Connection.Con nectionString = cCON

Connection.Ope n()

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

Dim Cmand As New OleDb.OleDbComm and

Dim CBuild As New OleDb.OleDbComm andBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill( DataSets, "Numbers")

CBuild = New OleDb.OleDbComm andBuilder(DAda pter)

'create pkey

With DataSets.Tables ("Numbers")

.PrimaryKey = New DataColumn() {.Columns("Numb er")}

End With

'create a select command with all parameters

DAdapter.Inser tCommand = CBuild.GetInser tCommand()

Dataroww = DataSets.Tables ("Numbers").New Row

Dataroww.Begin Edit()

Dataroww("Numb er") = "9999999"

Dataroww("Name ") = "KJHGFDSA"

Dataroww("Bloc k") = True

Dataroww.EndEd it()

DataSets.Table s("Numbers").Ro ws.Add(Dataroww )

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

DataSets.Accep tChanges()

[RobinS]

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******** ********@TK2MSF TNGP04.phx.gbl. ..

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

Thanks.......

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

"Ken Tucker [MVP]" <vb***@bellsout h.netwrote in message
news:eA******** ******@TK2MSFTN GP02.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******* *********@TK2MS FTNGP02.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.OleDbConn ection = New OleDb.OleDbConn ection

Dim cns As String = ""

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

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

Connection.Co nnectionString = cCON

Connection.Op en()

Dim DAdapter As OleDb.OleDbData Adapter = New
OleDb.OleDbDa taAdapter(Msql, Connection)

Dim Cmand As New OleDb.OleDbComm and

Dim CBuild As New OleDb.OleDbComm andBuilder

Dim DataSets As New DataSet

Dim Dataroww As DataRow

DAdapter.Fill (DataSets, "Numbers")

CBuild = New OleDb.OleDbComm andBuilder(DAda pter)

'create pkey

With DataSets.Tables ("Numbers")

.PrimaryKey = New DataColumn() {.Columns("Numb er")}

End With

'create a select command with all parameters

DAdapter.Inse rtCommand = CBuild.GetInser tCommand()

Dataroww = DataSets.Tables ("Numbers").New Row

Dataroww.Begi nEdit()

Dataroww("Num ber") = "9999999"

Dataroww("Nam e") = "KJHGFDSA"

Dataroww("Blo ck") = True

Dataroww.EndE dit()

DataSets.Tabl es("Numbers").R ows.Add(Datarow w)

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

DataSets.Acce ptChanges()

[Cor Ligthert [MVP]]

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("Numb er")}
End With

I don't see the sence for this one

DataSets.Accept Changes()

This one is included in the DataAdapter Update, only if you use partial
updates you need it.

Cor