Delete procedure can only delete one record

[thomas008]

I have a datable which contains my data. I display the data in a datagridview. All commands i use are stored procedures. The update and insert commands work fine. The deletecommand works fine when I delete one record and then update to the database. When delete more then one record i get:

 concurency violated: deletecommand affected 0 of the expected 1 records

Here is the code where i create the parameters and the command

 myConnection = New OleDbConnection
        myConnection.ConnectionString = myconnectionstring
        myCommand = New OleDbCommand
        myCommand.Connection = myConnection

        myCommand.CommandText = deletecommand
        myCommand.CommandType = CommandType.StoredProcedure

        parameter = New OleDbParameter
        parameter.ParameterName = "inroutingpointID"
        parameter.DbType = DbType.Double
        parameter.SourceColumn = "ID"
        parameter.SourceVersion = DataRowVersion.Original
        myCommand.Parameters.Add(parameter)
        'MessageBox.Show("deletecommand= " & myCommand.CommandText)
        myAdapter.DeleteCommand = myCommand

This is the stored procedure:

sSQL = "CREATE PROC procRoutingpointDelete(inroutingpointID LONG) AS DELETE * FROM routingpoint WHERE ID = inroutingpointID;"

I tried to change:

parameter.SourceVersion = DataRowVersion.Original

to:

parameter.SourceVersion = DataRowVersion.Current

But i still get the same error.

Can anyone help me?

 

[cjard]

In the Debugging Options, untick Just My Code
In the Debug Exceptions (might have to customize your toolbar for this, or press Ctrl+D then E) put a tick in the Thrown column of Common Language Runtime Exceptions

Cause the error. Code will break probably at an ExecuteQuery line; what is the value of the parameter of the db command, at the time the code breaks?
Can that ID be found in the db?

 

[thomas008]

I can't seem to find Debugging options. Where can i find this in microsoft visual basic express edition 2005? I did however put a tick in the throw column of common language runtim exceptions but i can't seem to see the value of the parameter yet.

 

[cjard]

In VS2005 proper (not express) I go Tools..Options..Debugging

Do you know how to use the debugger to inspect the values of variables?

 

[thomas008]

Yes i know how to check the variabels. But I am using the dataadapter(OLEDB but that doesnt matter i think) so i'm not sure i can see what values it gets because it calls the different methods according to the type of change in the dataset

 

[thomas008]

Ok i found where to look and the value of the parameter is 35. When i open my db in access i see a record with the ID of 35 so I think i should work

 

[cjard]

Are you sure that the db you opened in access and the db your app had open were the same file?

Are you sure that your parameter is 35 and the data is 35? Just because it looks like 35 (in either case) doesnt mean it doesnt have spaces on the end.

 

[thomas008]

I'm sure its the same db because if i change something in my db it shows up in my app and visa versa. I'm also sure there are no spaces on the end. Anyway i don't understand why i can delete one record in the datagridview then close my app so it updates to the db. but when i try this with 2 records it only deletes one and throws an error in the other case.

 

[cjard]

You're not trying to delete the same record twice are you?

 

[thomas008]

I'm not sure. Is there a way to see the tableadapter call every command when he needs it? because when i debug i only see the value where it goes wrong.

 

[Tom]

I dont think this would cause the problem that you are currently seeing but in your Create Procedure your declaring RoutingId as an Long but in your coding your passing a Double.

 

[cjard]

I'm not sure. Is there a way to see the tableadapter call every command when he needs it? because when i debug i only see the value where it goes wrong.

I didn't think you were using tableadapters, but the answer is no


Have a look at the rows returned by a call to datatable.GetChanges(RowState.Deleted)

does your datatable contain two rows having equal ids and a deleted rowstate?

 

[thomas008]

I dont think this would cause the problem that you are currently seeing but in your Create Procedure your declaring RoutingId as an Long but in your coding your passing a Double.

I changed the long and double with integer and everything still works the same.

Have a look at the rows returned by a call to datatable.GetChanges(RowState.Deleted)

does your datatable contain two rows having equal ids and a deleted rowstate?

This is quite confusing. When i call the table that i get from .GetChanges i see an empty datagridview. When i call the table after deleting a field i see a datagridview with one record but this record doesn't contain any values.
Maybe i should specify how i delete the records.
I simply set the property

DataGridView.AllowUserToDeleteRows

to true
So i can select a row and press delete on my keyboard. Maybe this way is wrong?

 

[cjard]

So i can select a row and press delete on my keyboard. Maybe this way is wrong?

Please post your project, in a zip file, WITHOUT the bin and obj folders

 

[thomas008]

This should be all the code you need. The code is a bit of a mess but i will sort it out when everything works properly.