Concurrency Violation 1

[Bluejay07]

Hello,

I am relatively new to VB.net and I would like some help. I am getting a concurrency violation when trying to do an update. I have done some research and tried various suggestions but nothing seems to work.

Here's the situation:
I create an SQL connection, add a new row to the dataset and update the database.

Public Sub Load_DefaultDataset()
'// Gets the default values from the database.

   Try
      Dim cmdbuilder As SqlClient.SqlCommandBuilder
      Dim dsNewRow As DataRow      'New dataset row.

      g_daDefaults = New SqlClient.SqlDataAdapter("SELECT * FROM Defaults", pihCONN)  'pihCONN is previously defined.

      g_dsDefaults.Clear()
      cmdbuilder = New SqlClient.SqlCommandBuilder(g_daDefaults)
      g_daDefaults.Fill(g_dsDefaults, "Defaults")

      '// Create a new datarow object.
      If g_dsDefaults.Tables("Defaults").Rows.Count = 0 Then
         dsNewRow = g_dsDefaults.Tables("Defaults").NewRow()     '// Create a new datarow.
         g_dsDefaults.Tables("Defaults").Rows.Add(dsNewRow)      '// Add the new row to the dataset.
      End If

      g_dsDefaults.Tables("Defaults").Rows(0).Item("LastLogin") = Today.Date
      g_daDefaults.Update(g_dsDefaults, "Defaults")
      
      Catch ex As Exception
         '// Use my own error handler.
         ErrorMessenger(Err.Number, ex.Message, "GlobalMod.Load_DefaultDataset")
      End Try
End Sub

After this, I have another section on the form with a checkbox and two text boxes. The text boxes are only enabled if the check box is checked.

The error occurs when checking the check box.

g_dsDefaults.Tables("Defaults").Rows(0).Item("UseLogin") = chkUseLogin.Checked
g_daDefaults.Update(g_dsDefaults, "Defaults")  [COLOR=red]<-- **ERROR**[/color]
g_dsDefaults.AcceptChanges()

How can I update the database at this point without receiving the error?

I will also be updating the database once the textbox values are entered.

The database is setup as such:
- DefaultID, int, primary key, Identity Specification is YES
- UseLogin, tinyint, null
- UserName, Varchar(80), null
- Password, varchar(80), null
- LastLogin, datetime, null

Any help would be greatly appreciated.

 

[jebenson]

A concurrency violation usually occurs when you try to update a record that has been deleted in the datatable, or the key used to find the record to update has changed. Do you have any code that could be making such a change before the referenced code runs?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Bluejay07]

Hi jebenson,

Thanks for the reply.

The code that I posted is the order in which the processes are happening. The only difference is that before the second update command I am enabling or disabling form controls (such as textboxes), which wouldn't affect the dataset.

The first update would use the SQL increment feature to create the ID value (primary key), therefore, ID would be '1' after the first update.

At this point, the record would now have a key. Does this affect the second update?

 

[jebenson]

Have you verified that a record is actually added to the database, from the first section of code?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Bluejay07]

The first section does indeed add a row to the database.

The primary key is created and the field LastLogin is updated with the current date.

For my testing, I keep deleting the row (in SQL) so that I am working with a blank database each time I start.

 

[jebenson]

Try ditching this line:

cmdbuilder = New SqlClient.SqlCommandBuilder(g_daDefaults)

and build the DataAdapter's InsertCommand, UpdateCommand and DeleteCommands manually. To get you started:

Dim InsertCmd As SqlCommand
Dim UpdateCmd As SqlCommand
Dim DeleteCmd As SqlCommand

Dim SQLStr As String

SQLStr = "Insert Into Defaults (UseLogin, UserName, Password, LastLogin) VALUES (@UseLogin, @UserName, @Password, @LastLogin)"

InsertCmd = New SQLCommand(SQLStr, pihCONN)

InsertCmd.CommandType = CommandType.Text

InsertCmd.Parameters.Add("@UseLogin", SqlDbType.Bit, 1, "UseLogin")
InsertCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 80, "UserName")
InsertCmd.Parameters.Add("@Password", SqlDbType.VarChar, 80, "Password")
InsertCmd.Parameters.Add("@LastLogin", SqlDbType.DateTime, 8, "LastLogin")

g_daDefaults.InsertCommand = InsertCmd

Do the same for the UpdateCommand and DeleteCommand. One trick to remember is that the parameters need to be added to the SqlCommand object in the same order they are listed in the SQL string.

Give this a try and let me know what happens, or if you need any help with the rest of the SQLCommands.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Bluejay07]

Thank you jebenson.

I will give your suggestion a try and I'll post the outcome when completed.

For future references, what difference does it make if the data adapter's update command is used versus the InsertCommand/UpdateCommand is used?

 

[jebenson]

The Update method of the DataAdapter is not the same thing as the InsertCommand, UpdateCommand and DeleteCommand. These 3 objects are the actual SqlCommand objects associated with the DataAdapter that do what their names say: insert, update or delete data. The Update method calls the InsertCommand, UpdateCommand and/or DeleteCommand as appropriate. You could have a DataTable with rows that have been inserted, some that have been updated and some deleted. The Update method will call all three commands when invoked in this situation.

I hope that clears it up a bit.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Bluejay07]

Thanks again for the response.

Since you are recommending me to get rid of

cmdbuilder = New SqlClient.SqlCommandBuilder(g_daDefaults)

are you also suggesting that I should have a SelectCommand for the initial search.

After reading your post, I came across the following:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.selectcommand.aspx

If this is the case, do I still use the line:
g_daDefaults.Fill(g_dsDefaults, "Defaults")

 

[jebenson]

This line of your code:

g_daDefaults = New SqlClient.SqlDataAdapter("SELECT * FROM Defaults", pihCONN)

sets up the DataAdapter's SelectCommand, so yes you can just call the Fill method.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Bluejay07]

Thank you very much Jebenson.

You have pointed me in the right direction. The InsertCommand works as does the UpdateCommand.

I am no longer receiving the "Concurrency Violation".

The code you provided was of great help.

Have a great day.