Error in updating database table from a dataset

S

Simon Verona

I have a problem with some code..

I'm adding records to an jet database using a data-adapter and a dataset.

The dataset and data-adapter are created using the following code:

==== code ====
Dim Connection As New System.Data.OleDb.OleDbConnection
Dim DataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim DataSet As New DataSet
Dim vmc As Int32
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\dms\webs\vehicles.mdb"
Connection.Open()
DataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
StockList", Connection)
DataAdapter.FillSchema(DataSet, SchemaType.Mapped, "StockList")
DataAdapter.Fill(DataSet, "StockList")
Dim CommandBuilder As System.Data.OleDb.OleDbCommandBuilder = New
OleDbCommandBuilder(DataAdapter)
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand
=== end of code ===

The Dataset is then updated row by row using an array :

===== code =====
Try
DataSet.Tables("StockList").Rows.Add(values)
Catch ex As Exception
Debug.WriteLine("Error : " & Ex.Message)
End Try
==== code ====

This doesn't cause any errors.
The data-adapter then updates the database using:

==== code =====
Try
DataAdapter.Update(DataSet.Tables("StockList"))
Catch ex As Exception
Debug.WriteLine("Error on Update : " & Ex.Message)
End Try
=============

This fails with the error : "Error on Update : Syntax error in INSERT INTO
statement."

I can't see how this can happen, nor what I need to do to debug the code!!

Any pointers?

Thanks in advance.
Simon
 
M

Marina

My guess would be that one of the columns in StockList has the name of a
reserved keyword. If so, set the QuotePrefix and QuoteSuffix properties to
[ and ] (I think access is ok with those), which should fix the problem. Or
else, just don't use reserved keywords for column names.

If this isn't the problem, you should call the GetInserCommand method of the
command builder to see what command it's trying to generate.
 
M

Miha Markic [MVP C#]

Hi Simon,
I don't think this line is required: DataAdapter.InsertCommand =
CommandBuilder.GetInsertCommand.
How does StockList table look like? Does it have primary key?
 
S

Simon Verona

The Insert command appears to be :

INSERT INTO StockList( StockNo , Make1 , Model , Derivative , EngineSize , EngineType , Transmission , Body Style , RegNo , Colour , RegDate , Retail , Mileage , Specification , RegYear , Option1 , Option2 , Option3 , Option4 , Option5 , Option6 , Option7 , Option8 , Option9 , Option10 , Option11 , Notes1 , Notes2 , Notes3 , Notes4 , Notes5 , Notes6 , Notes7 , Notes8 , Notes9 , Notes10 , SpecialVehicle , CarofWeek , DateInStock , DateExpected , Status , NewUsed , Location , SIV , ExpectedCost , CaravanType , ModelYear ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

I don't think there are any reserved words - unless there is a problem with "body style" ???



Regards



Simon

Marina said:
My guess would be that one of the columns in StockList has the name of a
reserved keyword. If so, set the QuotePrefix and QuoteSuffix properties to
[ and ] (I think access is ok with those), which should fix the problem. Or
else, just don't use reserved keywords for column names.

If this isn't the problem, you should call the GetInserCommand method of the
command builder to see what command it's trying to generate.

Simon Verona said:
I have a problem with some code..

I'm adding records to an jet database using a data-adapter and a dataset.

The dataset and data-adapter are created using the following code:

==== code ====
Dim Connection As New System.Data.OleDb.OleDbConnection
Dim DataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim DataSet As New DataSet
Dim vmc As Int32
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\dms\webs\vehicles.mdb"
Connection.Open()
DataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
StockList", Connection)
DataAdapter.FillSchema(DataSet, SchemaType.Mapped, "StockList")
DataAdapter.Fill(DataSet, "StockList")
Dim CommandBuilder As System.Data.OleDb.OleDbCommandBuilder = New
OleDbCommandBuilder(DataAdapter)
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand
=== end of code ===

The Dataset is then updated row by row using an array :

===== code =====
Try
DataSet.Tables("StockList").Rows.Add(values)
Catch ex As Exception
Debug.WriteLine("Error : " & Ex.Message)
End Try
==== code ====

This doesn't cause any errors.
The data-adapter then updates the database using:

==== code =====
Try
DataAdapter.Update(DataSet.Tables("StockList"))
Catch ex As Exception
Debug.WriteLine("Error on Update : " & Ex.Message)
End Try
=============

This fails with the error : "Error on Update : Syntax error in INSERT INTO
statement."

I can't see how this can happen, nor what I need to do to debug the code!!

Any pointers?

Thanks in advance.
Simon
Click to expand...
Click to expand...
 
S

Simon Verona

StockList doesn't have a primary key at the time of testing - I'm getting
other errors (duplicate keys on adding rows which I'm hoping to sort out wen
I see the data in the table!).

You should see the structure of the table in my other reply.

Regards
Simon
Miha Markic said:
Hi Simon,
I don't think this line is required: DataAdapter.InsertCommand =
CommandBuilder.GetInsertCommand.
How does StockList table look like? Does it have primary key?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Simon Verona said:
I have a problem with some code..

I'm adding records to an jet database using a data-adapter and a dataset.

The dataset and data-adapter are created using the following code:

==== code ====
Dim Connection As New System.Data.OleDb.OleDbConnection
Dim DataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim DataSet As New DataSet
Dim vmc As Int32
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\dms\webs\vehicles.mdb"
Connection.Open()
DataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
StockList", Connection)
DataAdapter.FillSchema(DataSet, SchemaType.Mapped, "StockList")
DataAdapter.Fill(DataSet, "StockList")
Dim CommandBuilder As System.Data.OleDb.OleDbCommandBuilder = New
OleDbCommandBuilder(DataAdapter)
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand
=== end of code ===

The Dataset is then updated row by row using an array :

===== code =====
Try
DataSet.Tables("StockList").Rows.Add(values)
Catch ex As Exception
Debug.WriteLine("Error : " & Ex.Message)
End Try
==== code ====

This doesn't cause any errors.
The data-adapter then updates the database using:

==== code =====
Try
DataAdapter.Update(DataSet.Tables("StockList"))
Catch ex As Exception
Debug.WriteLine("Error on Update : " & Ex.Message)
End Try
=============

This fails with the error : "Error on Update : Syntax error in INSERT INTO
statement."

I can't see how this can happen, nor what I need to do to debug the code!!

Any pointers?

Thanks in advance.
Simon
Click to expand...
Click to expand...
 
S

Simon Verona

I think it was the field "body style".. Setting the quoteprefix and quote suffix to [ and ] did the job.

Many thanks

Simon
The Insert command appears to be :

INSERT INTO StockList( StockNo , Make1 , Model , Derivative , EngineSize , EngineType , Transmission , Body Style , RegNo , Colour , RegDate , Retail , Mileage , Specification , RegYear , Option1 , Option2 , Option3 , Option4 , Option5 , Option6 , Option7 , Option8 , Option9 , Option10 , Option11 , Notes1 , Notes2 , Notes3 , Notes4 , Notes5 , Notes6 , Notes7 , Notes8 , Notes9 , Notes10 , SpecialVehicle , CarofWeek , DateInStock , DateExpected , Status , NewUsed , Location , SIV , ExpectedCost , CaravanType , ModelYear ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

I don't think there are any reserved words - unless there is a problem with "body style" ???



Regards



Simon

Marina said:
My guess would be that one of the columns in StockList has the name of a
reserved keyword. If so, set the QuotePrefix and QuoteSuffix properties to
[ and ] (I think access is ok with those), which should fix the problem. Or
else, just don't use reserved keywords for column names.

If this isn't the problem, you should call the GetInserCommand method of the
command builder to see what command it's trying to generate.

Simon Verona said:
I have a problem with some code..

I'm adding records to an jet database using a data-adapter and a dataset.

The dataset and data-adapter are created using the following code:

==== code ====
Dim Connection As New System.Data.OleDb.OleDbConnection
Dim DataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim DataSet As New DataSet
Dim vmc As Int32
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\dms\webs\vehicles.mdb"
Connection.Open()
DataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
StockList", Connection)
DataAdapter.FillSchema(DataSet, SchemaType.Mapped, "StockList")
DataAdapter.Fill(DataSet, "StockList")
Dim CommandBuilder As System.Data.OleDb.OleDbCommandBuilder = New
OleDbCommandBuilder(DataAdapter)
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand
=== end of code ===

The Dataset is then updated row by row using an array :

===== code =====
Try
DataSet.Tables("StockList").Rows.Add(values)
Catch ex As Exception
Debug.WriteLine("Error : " & Ex.Message)
End Try
==== code ====

This doesn't cause any errors.
The data-adapter then updates the database using:

==== code =====
Try
DataAdapter.Update(DataSet.Tables("StockList"))
Catch ex As Exception
Debug.WriteLine("Error on Update : " & Ex.Message)
End Try
=============

This fails with the error : "Error on Update : Syntax error in INSERT INTO
statement."

I can't see how this can happen, nor what I need to do to debug the code!!

Any pointers?

Thanks in advance.
Simon
Click to expand...
Click to expand...
 
B

Blake Weaver

Just a suggestion. In my limited experience, you could handle your Option
and Notes fields better by using a DataRelation to point to an Options Table
and another for a Notes Table. That way you can link as many Options/Notes
back to your StockList as you want with no wasted space.

Blake


The Insert command appears to be :

INSERT INTO StockList( StockNo , Make1 , Model , Derivative , EngineSize ,
EngineType , Transmission , Body Style , RegNo , Colour , RegDate , Retail ,
Mileage , Specification , RegYear , Option1 , Option2 , Option3 , Option4 ,
Option5 , Option6 , Option7 , Option8 , Option9 , Option10 , Option11 ,
Notes1 , Notes2 , Notes3 , Notes4 , Notes5 , Notes6 , Notes7 , Notes8 ,
Notes9 , Notes10 , SpecialVehicle , CarofWeek , DateInStock , DateExpected ,
Status , NewUsed , Location , SIV , ExpectedCost , CaravanType , ModelYear )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )
I don't think there are any reserved words - unless there is a problem with
"body style" ???

Regards

Simon
Marina said:
My guess would be that one of the columns in StockList has the name of a
reserved keyword. If so, set the QuotePrefix and QuoteSuffix properties to
[ and ] (I think access is ok with those), which should fix the problem. Or
else, just don't use reserved keywords for column names.

If this isn't the problem, you should call the GetInserCommand method of the
command builder to see what command it's trying to generate.

Simon Verona said:
I have a problem with some code..

I'm adding records to an jet database using a data-adapter and a dataset.

The dataset and data-adapter are created using the following code:

==== code ====
Dim Connection As New System.Data.OleDb.OleDbConnection
Dim DataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim DataSet As New DataSet
Dim vmc As Int32
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\dms\webs\vehicles.mdb"
Connection.Open()
DataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
StockList", Connection)
DataAdapter.FillSchema(DataSet, SchemaType.Mapped, "StockList")
DataAdapter.Fill(DataSet, "StockList")
Dim CommandBuilder As System.Data.OleDb.OleDbCommandBuilder = New
OleDbCommandBuilder(DataAdapter)
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand
=== end of code ===

The Dataset is then updated row by row using an array :

===== code =====
Try
DataSet.Tables("StockList").Rows.Add(values)
Catch ex As Exception
Debug.WriteLine("Error : " & Ex.Message)
End Try
==== code ====

This doesn't cause any errors.
The data-adapter then updates the database using:

==== code =====
Try
DataAdapter.Update(DataSet.Tables("StockList"))
Catch ex As Exception
Debug.WriteLine("Error on Update : " & Ex.Message)
End Try
=============

This fails with the error : "Error on Update : Syntax error in INSERT INTO
statement."

I can't see how this can happen, nor what I need to do to debug the code!!

Any pointers?

Thanks in advance.
Simon
Click to expand...
Click to expand...
 

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

update database from dataset 2
How save DataSet back to database? 1
OleDbCommandBuilder insert query will not work? dataset to Access 4
Row removed from a dataset table is not removed from a database tableduring update 2
The Microsoft jet engine cannot find the input table or query 6
Unable to update rows in a dataset. 1
SIMPLE update from dataset. 1
How do I make lots of changes to a database table using a dataset 2

Top