Syntax error (missing operator) when delete with Commandbuider

[Guest]

Hi I hope someone can help

I am creating a C# project that stores data into an Access database.

I have created a dataset using an OleDbDataAdapter with a sql select statment
"SELECT table.* FROM table

This table has a long Interger autonumbering ID key field, 1 text field, 1
Long Interger field, and 31 Double fields.

I then use the Commandbuilder object to create the Insert, delete and update
commands.

The Insert command seems to work ok as I can create a new row and use the
OleDbDataAdapter.update( ); with now problems

My problem is that I get Syntax error (missing operator) when I attempt to
update a record set after deleting a row.

After this I introduced a dataGrid control and connected this to the dataset
and all the data seems to be ok.

I have other tables in my project that a treated in a similar mannor and
these seem to delete rows ok. The only thing is that these other tables have
less fields but thats the only differance that I can see.

Any ideas?

 

[Cor Ligthert]

Hawkmoth,

I have other tables in my project that a treated in a similar mannor and
these seem to delete rows ok. The only thing is that these other tables
have
less fields but thats the only differance that I can see.

AFAIK is 100 fields the maximum for the commandbuilder.

I hope this helps,

Cor

 

[Guest]

Hi Cor

This has got less than 100 fields, there are only 34 fields.

In my project I handle all the database communications in a seporate class
but for experimental purposes I dropped an oleDbDataAdapter onto the form and
connected that up to the Access database and the the deletetion works ok.
Looking at the oleDbDataAdapter deletecommand in the properties and it
appears the same as the one created by the command biilder.

Thanks
Hawkmoth

 

[Cor Ligthert]

Hawkmoth,

Are you directly updating the original dataset.
One of the reasons that this can happen is with a construction with this (I
don't know what programlanguage you use)

if ds.haschanges = true then
da.update(ds.getchanges)
End if

And than not an
ds.acceptchanges.

Therefore show some code from this part, if it is not something like this.

Cor

 

[Guest]

Hi Cor
I sorry, I should have said, I am using C# in visual studio 2003.
I do copy the dataset inside the method that fills the form.

private DataSet dsDataBaseProjects;
protected internal void FillList(DataSet dsInstruments,DataSet dsDatabase)
{
//copy datasets
dsDataBaseProjects = dsDatabase.Copy();

I then edit the new dataset.

After pressing a button on the form the original dataset is updated.
Is this what is causeing it? perhaps it would be better if I either make the
original dataset global or passed it as a parameter.

This project is to produce a control that handles the communications with an
external rs232 device. As the project was getting quite large, I decided to
split the database functions into a seporate class.
This class is declared as a static in the main project control.
The form is used to allow user to manage Projects and Standard information
that can pass between the device and the Access database.

Thanks
Hawkmoth

 

[Cor Ligthert]

Hawkmoth,

For this is acceptchanges is for, when you know that your dataset is correct
processed, than you can do an acceptchanges on the copy of that as well.
Which deletes the datarows which are marked as deleted actually and does the
actual changes in the dataset and set all rowstates to "not changed".

I hope this clears it?

Cor

 

[Guest]

Hi Cor
Call me mister stupid but I'm still have trouble with it.
I was already using AcceptChanges etc on the second copy database but not on
the original.
So I decided it would be best not to copy the database as this seems a waste
of time anyway when I already have access the the dataset as a parameter of
the dataStorage class that I mentioned in my previous post.
I then added the Accept changes etc and it still failed.
I did notice one thing.
There are 9 tables in total in the database and I use a seporate dataAdapter
for each of them. Two of the tables that are earlyer in the routine that
updates them are deleting and updateing correctly.
So I'm a bit confused why it works on these and fails on 2 others. (Some
tables have nothing in).

 

[Cor Ligthert]

Hawkmoth,

You have set everything in try and catch blocks or are you using "using".

I would set them in try, catch, finally blocks in your case to get a good
control about what is happening. (all was it only for now and than change
it back).

Cor