Error Updating DataSet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dataset that I have used the .FillSchema method to add an empty copy of the underlying structure of an Access
table. I use an OledbCommandBuilder to generate the INSERT, UPDATE, statements

I then add quite a few records to the empty datatable within the dataset. This works fine

But when I run the .Update method at the end of the process, to try to add the records to the underlying table,
it falls over with an error whose message says 'Syntax Error in INSERT statement'. It is not, however, possible to examine the syntax of the SQL used, because it is automatically generated by the OleDbCommandBuilder. Using the debugger to access the .GetInsertCommand.CommandText method of the OleDbDataAdapter just brings back a skeletal INSERT statement with lots of placeholders/question marks in it that doesn't help much

The relevant sections of the code I am running are as follows

Dim cnOut as OleDbConnection, dsOut as DataSet, daArticles as OleDbDataAdapter, cbArticles as OleDbCommanBuilde
Dim sSQL as String, iAns as Int16, sDBOutFull as Strin

sDBOutFill = FilesOutput.Path & "\" & FilesOutput.SelectedItem '(FilesOutput is a FileListBox control

[...

cnOut = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBOutFull & ""
sSQL = "SELECT * FROM articles
dsOut = New DataSet(
daArticles = New OleDbDataAdapter(sSQL, cnOut
cbArticles = New OleDbCommandBuilder(daArticles
daArticles.FillSchema(dsOut, SchemaType.Source, "articles"

[then a section of code that adds, apparently quite sucessfully, around 1300 records to the empty 'articles' DataTable within the 'dsOut' DataSet

and to update

iAns = MessageBox.Show(dsOut.Tables(0).Rows.Count.ToString & " records are ready to be added to the 'articles' table in " & FilesOutput.SelectedItem & ". Proceed?", "Insert Records", MessageBoxButtons.YesNo, MessageBoxIcon.Question
If iAns = vbYes The
'add the records accordingly, if possibl
cnOut.Open(
With daArticle
.Update(dsOut, "articles"
End Wit
cnOut.Close(
End I

It falls over on the .Update line above as indicated earlier

Any ideas why this might happen

Thank

Ian
 
Ian said:
I have a dataset that I have used the .FillSchema method to add an
empty copy of the underlying structure of an Access
table. I use an OledbCommandBuilder to generate the INSERT, UPDATE,
statements.
Click to expand...

I would strongly recommend that you explicitly set the
insert/update/delete statements instead. Call me cynical, but I don't
like to trust such important things to the relatively black magic of
OledbCommandBuilder...
 
Back
Top