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.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()

 

[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]

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

Thanks.......

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

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
---------------------------

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()

 

[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.
------------------------------

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

Thanks.......

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

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
---------------------------

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()

 

[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("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