Error in updating database table from a dataset

  • Thread starter Thread starter Simon Verona
  • Start date Start date
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
 
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.
 
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?
 
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...
 
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...
 
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...
 
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...
 
Back
Top