Error on adding a record to an Access Database.

D

Dustin Wilson

I tried to write a record to an access database with the following code but
I get an error on the line:

da.Update(ds, "tblDrawingList")

The error text that comes up is as follows:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at DatabaseWriteTest.Form1.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\DWilson\My Documents\Visual Studio
Projects\DatabaseWriteTest\Form1.vb:line 120

Microsoft JET Database Engine

Does anyone know what I'm doing wrong here?

Thanks

Dustin





Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim conn As OleDbConnection

Dim da As OleDbDataAdapter

Dim cb As OleDbCommandBuilder

Dim ds As DataSet

Dim drNewRow As DataRow

Dim strConnection As String

Dim strSQL As String

Try

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory() &
"CADTools.mdb"

strSQL = "SELECT * FROM tblDrawingList;"

conn = New OleDbConnection(strConnection)

da = New OleDbDataAdapter(strSQL, conn)

cb = New OleDbCommandBuilder(da)

ds = New DataSet

da.FillSchema(ds, SchemaType.Source, "tblDrawingList")

drNewRow = ds.Tables("tblDrawingList").NewRow()

drNewRow(0) = "8"

drNewRow(1) = "8"

drNewRow(2) = "0"

drNewRow(3) = "0"

ds.Tables("tblDrawingList").Rows.Add(drNewRow)

da.Update(ds, "tblDrawingList")

drNewRow = Nothing

ds.Dispose()

cb.Dispose()

da.Dispose()

conn.Close()

Catch ex As Exception

MsgBox(ex.ToString & vbCrLf & vbCrLf & ex.Source)

End Try

End Sub
 
C

Cor Ligthert [MVP]

Dustin,

At least it is possible that the row already exist (by instance when you
process this twice)

You use an awfull lot of code by the way.
did you know that you can do in VBNet as well
dim conn as new OledBConnection("The Connection String)

and more of those.

While the only thing you need in my opinion at the end is

conn.close or conn.dispose in this case

Your try block can better look like this
\\\
Try
conn.open
Try
'do commands
Catch ex
'handle ex for the commands
End Try
Catch ex
'handle ex for the connection
Finally
conn.close
End try
///

I hope this helps,

Cor
 
D

David Lloyd

Dustin:

Another suggestion would be to use the Debug class of the System.Diagnostic
namespace to write out the CommandText property of the InsertCommand of your
data adapter. In this way, you can verify that the SQL INSERT statement
generated by the OleDbCommandBuilder is syntactically correct.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I tried to write a record to an access database with the following code but
I get an error on the line:

da.Update(ds, "tblDrawingList")

The error text that comes up is as follows:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at DatabaseWriteTest.Form1.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\DWilson\My Documents\Visual Studio
Projects\DatabaseWriteTest\Form1.vb:line 120

Microsoft JET Database Engine

Does anyone know what I'm doing wrong here?

Thanks

Dustin





Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim conn As OleDbConnection

Dim da As OleDbDataAdapter

Dim cb As OleDbCommandBuilder

Dim ds As DataSet

Dim drNewRow As DataRow

Dim strConnection As String

Dim strSQL As String

Try

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory() &
"CADTools.mdb"

strSQL = "SELECT * FROM tblDrawingList;"

conn = New OleDbConnection(strConnection)

da = New OleDbDataAdapter(strSQL, conn)

cb = New OleDbCommandBuilder(da)

ds = New DataSet

da.FillSchema(ds, SchemaType.Source, "tblDrawingList")

drNewRow = ds.Tables("tblDrawingList").NewRow()

drNewRow(0) = "8"

drNewRow(1) = "8"

drNewRow(2) = "0"

drNewRow(3) = "0"

ds.Tables("tblDrawingList").Rows.Add(drNewRow)

da.Update(ds, "tblDrawingList")

drNewRow = Nothing

ds.Dispose()

cb.Dispose()

da.Dispose()

conn.Close()

Catch ex As Exception

MsgBox(ex.ToString & vbCrLf & vbCrLf & ex.Source)

End Try

End Sub
 
D

Dustin Wilson

I tried adding the line following line
da.InsertCommand = cb.GetInsertCommand

just before the da.update(ds, "tblDrawingList") command. looking at the
InsertCommand CommandText in Debug mode I get:
"INSERT INTO tblDrawingList( LR-UN1 , LR-UN2 , LR-UN3 , LR-UN4 ) VALUES ( ?
, ? , ? , ? )"

I get the same error of syntax error in INSERT INTO statement when I try to
step the da.update line. I also added the conn.close and conn.dispose
methods but the code never makes it that far to execute those statements. I
think it has something to do with the command builder since the values of
the insert into command are questions marks. does anyone know what I'm
doing wrong and how to fix it?

Thanks
Dustin
 
D

Dustin Wilson

I did this and get a SQL INSERT statement that looks like:
"INSERT INTO tblDrawingList( LR-UN1 , LR-UN2 , LR-UN3 , LR-UN4 ) VALUES ( ?
, ? , ? , ? )"
I'm not sure how to fix this.. I though the line drNewRow(0)="8" etc. were
to fill those values in. Do you know what I'm doing wrong?

Thanks
Dustin
 
P

Paul Clement

¤ I tried to write a record to an access database with the following code but
¤ I get an error on the line:
¤
¤ da.Update(ds, "tblDrawingList")
¤
¤ The error text that comes up is as follows:
¤
¤ System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
¤ at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
¤ DataTableMapping tableMapping)
¤ at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
¤ srcTable)
¤ at DatabaseWriteTest.Form1.Button1_Click(Object sender, EventArgs e) in
¤ C:\Documents and Settings\DWilson\My Documents\Visual Studio
¤ Projects\DatabaseWriteTest\Form1.vb:line 120
¤
¤ Microsoft JET Database Engine
¤
¤ Does anyone know what I'm doing wrong here?
¤

Do you have a list of the column names in tblDrawingList. I'm guessing you might be using a reserved
word. You might want to post your InsertCommand.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
C

Cor Ligthert [MVP]

Dustin,

Does your table have a primary key, I get the idea that it has not or that
you are not giving it.
And than are you sure that it does not exist already?

Cor
 
D

Dustin Wilson

Paul

The column names are:
"LR-UN1","LR-UN2","LR-UN3" and "LR-UN4".

I also tried substituting the line
drNewRow(0) = "8" with drNewRow("LR-UN1") = "8" and still the same result.

I originally had other fields in the database but I stripped them all out
and left only the four fields in just to see if it would work but the same
result.

The insert command I had was:
"INSERT INTO tblDrawingList( LR-UN1 , LR-UN2 , LR-UN3 , LR-UN4 ) VALUES ( ?
, ? , ? , ? )"

I had to add the following line just before my da.update command though to
get any insert command at all.
da.InsertCommand = cb.GetInsertCommand

Thanks
Dustin

Paul Clement said:
¤ I tried to write a record to an access database with the following code but
¤ I get an error on the line:
¤
¤ da.Update(ds, "tblDrawingList")
¤
¤ The error text that comes up is as follows:
¤
¤ System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
¤ at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
¤ DataTableMapping tableMapping)
¤ at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
¤ srcTable)
¤ at DatabaseWriteTest.Form1.Button1_Click(Object sender, EventArgs e) in
¤ C:\Documents and Settings\DWilson\My Documents\Visual Studio
¤ Projects\DatabaseWriteTest\Form1.vb:line 120
¤
¤ Microsoft JET Database Engine
¤
¤ Does anyone know what I'm doing wrong here?
¤

Do you have a list of the column names in tblDrawingList. I'm guessing you might be using a reserved
word. You might want to post your InsertCommand.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
 
D

Dustin Wilson

Cor

Originally my database did have an id field that was an autonumber field and
the primary key. I though this might be the problem so I also tried
deleting that field from the database so that all I had was 4 text field in
the database with no primary key and I still get an error. The database
also has no records in it.

Regards
Dustin
 
M

Mark Ashton

Most likely Access is getting confused by the - in the column names.
Have you tried setting the OleDbCommandBuilder.QuotePrefix and QuoteSuffix
properties?
access uses a backwards single quote for the QuotePrefix and normal single
quote for the QuoteSuffix.
Dim builder as new OleDbCommandBuilder
builder.QuotePrefix = "`"
builder.QuoteSuffix = "'"
builder.Adapter = adapter
Print builder.GetInsertCommand.CommandText

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Dustin Wilson said:
Paul

The column names are:
"LR-UN1","LR-UN2","LR-UN3" and "LR-UN4".

I also tried substituting the line
drNewRow(0) = "8" with drNewRow("LR-UN1") = "8" and still the same result.

I originally had other fields in the database but I stripped them all out
and left only the four fields in just to see if it would work but the same
result.

The insert command I had was:
"INSERT INTO tblDrawingList( LR-UN1 , LR-UN2 , LR-UN3 , LR-UN4 ) VALUES
( ?
, ? , ? , ? )"

I had to add the following line just before my da.update command though to
get any insert command at all.
da.InsertCommand = cb.GetInsertCommand

Thanks
Dustin

Paul Clement said:
¤ I tried to write a record to an access database with the following code but
¤ I get an error on the line:
¤
¤ da.Update(ds, "tblDrawingList")
¤
¤ The error text that comes up is as follows:
¤
¤ System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.
¤ at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
¤ DataTableMapping tableMapping)
¤ at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
¤ srcTable)
¤ at DatabaseWriteTest.Form1.Button1_Click(Object sender, EventArgs e) in
¤ C:\Documents and Settings\DWilson\My Documents\Visual Studio
¤ Projects\DatabaseWriteTest\Form1.vb:line 120
¤
¤ Microsoft JET Database Engine
¤
¤ Does anyone know what I'm doing wrong here?
¤

Do you have a list of the column names in tblDrawingList. I'm guessing
you might be using a reserved
word. You might want to post your InsertCommand.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
Click to expand...
 
C

Cor Ligthert [MVP]

Dustin,

I tried your code and changed it.

\\\\
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory() &
"CADTools.mdb"
Dim conn As New OleDbConnection(strConnection)
Dim da As New OleDbDataAdapter("SELECT * FROM tblDrawingList", conn)
Dim cb As New OleDbCommandBuilder(da)
Try
conn.Open()
Try
Dim ds As New DataSet
da.FillSchema(ds, SchemaType.Source, "tblDrawingList")
Dim drNewRow As DataRow =
ds.Tables("tblDrawingList").NewRow()
drNewRow(0) = "8"
drNewRow(1) = "8"
drNewRow(2) = "0"
drNewRow(3) = "0"
ds.Tables("tblDrawingList").Rows.Add(drNewRow)
da.Update(ds, "tblDrawingList")
Catch ex As Exception
MsgBox(ex.ToString & vbCrLf & vbCrLf & ex.Source)
End Try
Catch
MessageBox.Show("there was a connection errror")
Finally
conn.Close()
End Try
End Sub
////

At least there is an semicolon in your SQL string that should not be there.

I saw that quick however did want to show this in my opinion cleaner code.

I hope this helps,

Cor
 
D

Dustin Wilson

Mark

That was totally the problem.. As soon as I changed the Field names from
"LR-UN1" to "LRUN1" etc.. the code ran fine. Really odd I've never had that
problem before but that was with VB6 and this was my first try with VB.NET.
I even added my AutoIncrementing index back in and worked fine as well.
I'll change my code and database names to avoid the dash "-" but is there a
way to get it to work properly in the future with dash?

Since i'm new to VB.NET though I'm curious what you are refering to in
regards to the QuotePrefix and QuoteSuffix?

Thanks
Dustin

Mark Ashton said:
Most likely Access is getting confused by the - in the column names.
Have you tried setting the OleDbCommandBuilder.QuotePrefix and QuoteSuffix
properties?
access uses a backwards single quote for the QuotePrefix and normal single
quote for the QuoteSuffix.
Dim builder as new OleDbCommandBuilder
builder.QuotePrefix = "`"
builder.QuoteSuffix = "'"
builder.Adapter = adapter
Print builder.GetInsertCommand.CommandText

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Dustin Wilson said:
Paul

The column names are:
"LR-UN1","LR-UN2","LR-UN3" and "LR-UN4".

I also tried substituting the line
drNewRow(0) = "8" with drNewRow("LR-UN1") = "8" and still the same result.

I originally had other fields in the database but I stripped them all out
and left only the four fields in just to see if it would work but the same
result.

The insert command I had was:
"INSERT INTO tblDrawingList( LR-UN1 , LR-UN2 , LR-UN3 , LR-UN4 ) VALUES
( ?
, ? , ? , ? )"

I had to add the following line just before my da.update command though to
get any insert command at all.
da.InsertCommand = cb.GetInsertCommand

Thanks
Dustin

¤ I tried to write a record to an access database with the following
Click to expand...
code
but
¤ I get an error on the line:
¤
¤ da.Update(ds, "tblDrawingList")
¤
¤ The error text that comes up is as follows:
¤
¤ System.Data.OleDb.OleDbException: Syntax error in INSERT INTO
statement.
¤ at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
¤ DataTableMapping tableMapping)
¤ at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
¤ srcTable)
¤ at DatabaseWriteTest.Form1.Button1_Click(Object sender, EventArgs
Click to expand...
e)
in
¤ C:\Documents and Settings\DWilson\My Documents\Visual Studio
¤ Projects\DatabaseWriteTest\Form1.vb:line 120
¤
¤ Microsoft JET Database Engine
¤
¤ Does anyone know what I'm doing wrong here?
¤

Do you have a list of the column names in tblDrawingList. I'm guessing
you might be using a reserved
word. You might want to post your InsertCommand.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
D

Dustin Wilson

Cor

I ended up getting it working. The problem turned out to be the database
column names having a dash "-" in them as Mark Ashton mentioned. As soon as
I removed the dash from the column name it worked.

I do appreciate your comments though on compressing my code down to make it
more compact and have rewritten my code.

Thanks
Dustin
 
P

Paul Clement

¤ Mark
¤
¤ That was totally the problem.. As soon as I changed the Field names from
¤ "LR-UN1" to "LRUN1" etc.. the code ran fine. Really odd I've never had that
¤ problem before but that was with VB6 and this was my first try with VB.NET.
¤ I even added my AutoIncrementing index back in and worked fine as well.
¤ I'll change my code and database names to avoid the dash "-" but is there a
¤ way to get it to work properly in the future with dash?
¤
¤ Since i'm new to VB.NET though I'm curious what you are refering to in
¤ regards to the QuotePrefix and QuoteSuffix?

I have to say the use of the hyphen never dawned on me. Good catch by Mark.

How about if you enclose the column names within brackets? Access typically adds the brackets during
query design to avoid these types of issues.


Paul
~~~~
Microsoft MVP (Visual Basic)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding a record to a Microsoft Jet database 5
I'm going slightly mad - OleDbCommandBuilder 1
Adding a record to an Access db using DataAdapter.Update 1
DataAdapter + DataRow + Inserting a record 1
problem adding new record to access database table 2
Operation must use an updateable query. 4
Inserting Access Table from VB.Net 4
Database Update 3

Top