[RESOLVED] [VS2008] Concurrency

[Minolwen]

DBConcurrencyException was unhandled.
Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.

Above is the error message received when doing the following:
Using a binding source for the datagrid I populate textboxes for the row that is currently selected. The problem occurs when I modify one of these textboxes, then delete a different row, then save (in that order). The following are the two scripts used in this process, if you need more I'd be happy to supply them.

Delete Script:

    Private Sub cmdMCDeleteRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMCDeleteRecord.Click

        Dim PK As Integer = CInt(Me.dgvMyCigars.CurrentRow.Cells("ID").Value)
        Dim MCconnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\cdb.rmd;Persist Security Info=False;")
        Dim MCcommand As New OleDb.OleDbCommand("DELETE * FROM CigarTable WHERE ID = @ID", MCconnection)

        Try

            If MsgBox("Are you sure you wish to delete the current cigar?", MsgBoxStyle.YesNo, "eHumidor - Delete Record Confirmation") = MsgBoxResult.Yes Then

                Me.MCBind.EndEdit()
                DirectCast(Me.MCBind.Current, DataRowView).Delete()
                Me.MCBind.EndEdit()

                MCcommand.Parameters.AddWithValue("@ID", PK)

                MCconnection.Open()
                'Open Connection
                MCcommand.ExecuteNonQuery()
                MCconnection.Close()

                Appstatus = "Cigar Successfully Deleted!"
                Status()

            End If

        Catch ex As Exception

            Appstatus = "Error 004 - Cigar not deleted.  Please submit bug report!"
            Status()
            MsgBox(ex.Message)

        Finally

            If MCconnection.State = ConnectionState.Open Then
                MCconnection.Close()
            End If

        End Try

    End Sub

Save Script:

        Try
            Dim MCCmdBldr As OleDbCommandBuilder

            Me.MCBind.EndEdit()
            MCCmdBldr = New OleDbCommandBuilder(Me.MCadapter)
            Debug.WriteLine(MCCmdBldr.GetUpdateCommand.CommandText())
            Me.MCadapter.Update(Me.MCDS, "CigarTable")
            Me.MCconnection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

I've read MSDN on concurrency errors and while I can make sense of what concurrency is, I cannot see why I'm having this problem.

[onlyGirl]

Try executing the delete command directly on the database, I'm assuming it's Access. It should help you out a little more. Make sure that the id is in the db.

[Minolwen]

Do you mean opening the database and try using the same command that I used above? Otherwise, it seems to me (a novice at best) that I am deleting directly from the database using:

Dim MCcommand As New OleDb.OleDbCommand("DELETE * FROM CigarTable WHERE ID = @ID", MCconnection)

No?

Ok, after your suggestion I went back and tested again. Turns out you do not have to modify a record first, that was just coincidence. All I have to do is delete, using the script above (which succeeds, record gone from table and database), then save. On the save is the error, using the script above.

[onlyGirl]

Yes, open the database and click on query's and try to execute the command there.

But before you do that, I'm curious as to what will happen if you change the parameters to this:

MCcommand.Parameters.Add("@ID", OleDbType.Integer(specify the size here),"ID")

[Minolwen]

Parameter @ID has no default value.

I have tried both yours and mine:

This is in place already:

Dim PK As Integer = CInt(Me.dgvMyCigars.CurrentRow.Cells("ID").Value)

I have tried yours:

MCcommand.Parameters.Add("@ID", OleDbType.Integer, 5 ,"ID")

and mine:

MCcommand.Parameters.Add("@ID", OleDbType.Integer, 5, PK.ToString)

They all lead to the error at the top of this post, however the record in every situation, gets deleted from the database.

[onlyGirl]

I have tried both yours and mine:

This is in place already:

Dim PK As Integer = CInt(Me.dgvMyCigars.CurrentRow.Cells("ID").Value)

I have tried yours:

MCcommand.Parameters.Add("@ID", OleDbType.Integer, 5 ,"ID")

and mine:

MCcommand.Parameters.Add("@ID", OleDbType.Integer, 5, PK.ToString)

They all lead to the error at the top of this post, however the record in every situation, gets deleted from the database.

What is ID's datatype in the db?

[Minolwen]

Ok, I ran:

DELETE * FROM CigarTable WHERE ID = 243

Ok, I picked a random ID that was in the table. But when I ran this in Access (07), I went back to the table and the row was there but said "DELETED" in every cell. I had to restart access for the row to actually be gone.

Not sure if this helps.

[Minolwen]

Long Integer.

[onlyGirl]

It has to do with the datatypes, I believe.

Try executing the command in Access again but this time put 243 as '243'

[Minolwen]

The action or event was blocked by disabled mode.

Message at the bottom of the screen in access.

[onlyGirl]

Message at the bottom of the screen in access.

I've never had that error before but I did a search and this is what I found:
http://office.microsoft.com/en-us/ac...564031033.aspx

[Minolwen]

Data type mismatch in criteria expression.

Now that I am trusted by myself, I ran it again and the above error is what I received.

[onlyGirl]

Now that I am trusted by myself, I ran it again and the above error is what I received.

Like I said, it has to do with the way you declared ID. Try messing with the different options of Number.

[Minolwen]

Well, I appreciate the help. I'm not getting a concurrency error anymore, in fact I've been messing with it trying to figure it out and search google/msdn so much I'm not sure when that error went away.

Now the only error I get is the:

Parameter @ID has no default value.

However the record still deletes and the program does not stop, just displays that error message (probably due to the Try statement it is wrapped in ).

[onlyGirl]

Put a breakpoint at the parameter and hover over textbox1 to check the value.

[onlyGirl]

Well, I appreciate the help. I'm not getting a concurrency error anymore, in fact I've been messing with it trying to figure it out and search google/msdn so much I'm not sure when that error went away.

Now the only error I get is the:



However the record still deletes and the program does not stop, just displays that error message (probably due to the Try statement it is wrapped in ).

The error went away because you changed disabled mode. If you need more help, you can PM me.

[Minolwen]

Sometimes the amount of ignorance I shed in a day amazes me. I was deleting the record twice (three times depending on how you see it)...

DirectCast(Me.MCBind.Current, DataRowView).Delete()

Marked it for deleting via the binding source... (You probably see where I'm going already)

MCcommand.ExecuteNonQuery()

Deleted it from the database.

Then finally updating on exit attempted to delete it again because of the binding source. So I commented out the delete directly from the database, kept the first code above and it deletes on exit now.

Haven't determined if this is the best method which is why I only commented it out and not deleted it. Will consider the rest of the app before going further. I do greatly appreciate your help though, thank you.

[onlyGirl]

Sometimes the amount of ignorance I shed in a day amazes me. I was deleting the record twice (three times depending on how you see it)...

DirectCast(Me.MCBind.Current, DataRowView).Delete()

Marked it for deleting via the binding source... (You probably see where I'm going already)

MCcommand.ExecuteNonQuery()

Deleted it from the database.

Then finally updating on exit attempted to delete it again because of the binding source. So I commented out the delete directly from the database, kept the first code above and it deletes on exit now.

Haven't determined if this is the best method which is why I only commented it out and not deleted it. Will consider the rest of the app before going further. I do greatly appreciate your help though, thank you.

Stick with ExecuteNonQuery. LOL, I can understand I've made mistakes like that. You're welcome.