Syntax error in INSERT INTO statement

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

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

>
Dim cns As String = ""

'create pkey
With DataSets.Tables("Numbers")
.PrimaryKey = New DataColumn() {.Columns("Number")}
End With

DataSets.AcceptChanges()