sgaggerj
asked on
VB.NET MySQL Concurrency Violation
Hello Experts!
I have a dataset that was created by the IDE, selecting tables only, no procedures or anything else.
I Extended the dataset so I could fill tables using different parameters
Declaring the tables and adapter I want to use....
Now filling the table ...
Getting the data I need to work with
I use the id to greatly reduce the number of records in the data table - in a real application of this app, there could be 10, 1000, or 15000 records. rather than pull them all, i pull just the one i need.
I now modify 1 value in the table in the sub Save()...
and try to save the data
MyTableAdapter.Update(MyTa ble)
gives the concurrency error
"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
I've been looking around at how to solve this, and one of the more prevalent solutions is to add a 'timestamp' column. This is *not* possible as I need to work with the db as it is.
I am the only one working with the db, and nowhere else is this data being modified.
The table has 1 primary key, 'id' (integer)
The table is read from in other places, but not modified in any way.
What am i missing?
Here's the rest of possible pertinent information
here's the SQL to create the table:
here is the auto-generated update code
I have a dataset that was created by the IDE, selecting tables only, no procedures or anything else.
I Extended the dataset so I could fill tables using different parameters
Public Function FillByID(ByVal datatable As MyDataSet.Table1DataTable, ByVal id As String) As Integer
Dim stSelect As String
stSelect = Me._commandCollection(0).CommandText
Try
Me._commandCollection(0).CommandText += " WHERE id='" + id + "'"
Return Me.Fill(datatable)
Catch ex As Exception
Return -1
Finally
Me._commandCollection(0).CommandText = stSelect
End Try
End Function
Declaring the tables and adapter I want to use....
Private RowData as MyDataSet.Table1Row
Private MyTable as MyDataSet.Table1
Private MyTableAdapter as MyDataSetTableAdapters.Table1TableAdapter
Private Sub CheckTables()
MyTable = New MyDataSet.Table1
MyTableAdapter = New MyDataSetTableAdapters.Table1TableAdapter
MyTableAdapter.ClearBeforeFill = True
MyTableAdapter.Connection = New MySQLConnection(connectionString)
End Sub
Now filling the table ...
MytableAdapter.FillByID(MyTable,"3")
Getting the data I need to work with
RowData = DirectCast(MyTable.Select("id='3'")(0),MyTable.Table1Row)
I use the id to greatly reduce the number of records in the data table - in a real application of this app, there could be 10, 1000, or 15000 records. rather than pull them all, i pull just the one i need.
I now modify 1 value in the table in the sub Save()...
Private Sub Save()
' Current value of RowData.name = "MyValue1"
RowData.name = "TestValue2"
End Sub
and try to save the data
Try
If RowData Is Nothing Then
MsgBox("No table selected.", MsgBoxStyle.OkOnly, "Error")
Exit Sub
End If
Save() ' this does the actual updating of the information in the row, see above
If MyTable.GetChanges IsNot Nothing Then
RowData.EndEdit()
If MyTableAdapter.Update(MyTable) > 0 Then
Mytable.AcceptChanges()
ChangedList.Clear()
MsgBox("Table saved.", MsgBoxStyle.OkOnly, "Success")
Else
MsgBox("Table not updated.", MsgBoxStyle.OkOnly, "Nothing to update")
End If
Else
MsgBox("Nothing to update.", MsgBoxStyle.OkOnly, "No changes made")
End If
Catch ex As Exception
MsgBox("Error Updating Table." + vbLf + ex.Message, MsgBoxStyle.OkOnly, "Error")
End Try
MyTableAdapter.Update(MyTa
gives the concurrency error
"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
I've been looking around at how to solve this, and one of the more prevalent solutions is to add a 'timestamp' column. This is *not* possible as I need to work with the db as it is.
I am the only one working with the db, and nowhere else is this data being modified.
The table has 1 primary key, 'id' (integer)
The table is read from in other places, but not modified in any way.
What am i missing?
Here's the rest of possible pertinent information
here's the SQL to create the table:
CREATE TABLE `character_` (
`id` int(11) NOT NULL auto_increment,
`account_id` int(11) NOT NULL default '0',
`name` varchar(64) NOT NULL default '',
`profile` blob,
`timelaston` int(11) unsigned default '0',
`x` float NOT NULL default '0',
`y` float NOT NULL default '0',
`z` float NOT NULL default '0',
`zonename` varchar(30) NOT NULL default '',
`alt_adv` blob,
`zoneid` smallint(6) NOT NULL default '0',
`instanceid` SMALLINT UNSIGNED DEFAULT '0' NOT NULL,
`pktime` int(8) NOT NULL default '0',
`inventory` blob,
`groupid` int(10) unsigned NOT NULL default '0',
`extprofile` blob,
`class` tinyint(4) NOT NULL default '0',
`level` mediumint(8) unsigned NOT NULL default '0',
`lfp` tinyint(1) unsigned NOT NULL default '0',
`lfg` tinyint(1) unsigned NOT NULL default '0',
`mailkey` CHAR(16) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `account_id` (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
here is the auto-generated update code
Me._adapter.UpdateCommand = New Global.MySql.Data.MySqlClient.MySqlCommand()
Me._adapter.UpdateCommand.Connection = Me.Connection
Me._adapter.UpdateCommand.CommandText = "UPDATE `character_` SET `account_id` = @account_id, `name` = @name, `profile` = @"& _
"profile, `timelaston` = @timelaston, `x` = @x, `y` = @y, `z` = @z, `zonename` = "& _
"@zonename, `alt_adv` = @alt_adv, `zoneid` = @zoneid, `instanceid` = @instanceid,"& _
" `pktime` = @pktime, `inventory` = @inventory, `groupid` = @groupid, `extprofile"& _
"` = @extprofile, `class` = @class, `level` = @level, `lfp` = @lfp, `lfg` = @lfg,"& _
" `mailkey` = @mailkey WHERE ((`id` = @Original_id) AND (`account_id` = @Original"& _
"_account_id) AND (`name` = @Original_name) AND ((@IsNull_timelaston = 1 AND `tim"& _
"elaston` IS NULL) OR (`timelaston` = @Original_timelaston)) AND (`x` = @Original"& _
"_x) AND (`y` = @Original_y) AND (`z` = @Original_z) AND (`zonename` = @Original_"& _
"zonename) AND (`zoneid` = @Original_zoneid) AND (`instanceid` = @Original_instan"& _
"ceid) AND (`pktime` = @Original_pktime) AND (`groupid` = @Original_groupid) AND "& _
"(`class` = @Original_class) AND (`level` = @Original_level) AND (`lfp` = @Origin"& _
"al_lfp) AND (`lfg` = @Original_lfg) AND (`mailkey` = @Original_mailkey))"
Me._adapter.UpdateCommand.CommandType = Global.System.Data.CommandType.Text
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@account_id"
param.DbType = Global.System.Data.DbType.Int32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int32
param.IsNullable = true
param.SourceColumn = "account_id"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@name"
param.DbType = Global.System.Data.DbType.[String]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.VarChar
param.IsNullable = true
param.SourceColumn = "name"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@profile"
param.DbType = Global.System.Data.DbType.[Object]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Blob
param.IsNullable = true
param.SourceColumn = "profile"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@timelaston"
param.DbType = Global.System.Data.DbType.UInt32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt32
param.IsNullable = true
param.SourceColumn = "timelaston"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@x"
param.DbType = Global.System.Data.DbType.[Single]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Float
param.IsNullable = true
param.SourceColumn = "x"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@y"
param.DbType = Global.System.Data.DbType.[Single]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Float
param.IsNullable = true
param.SourceColumn = "y"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@z"
param.DbType = Global.System.Data.DbType.[Single]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Float
param.IsNullable = true
param.SourceColumn = "z"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@zonename"
param.DbType = Global.System.Data.DbType.[String]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.VarChar
param.IsNullable = true
param.SourceColumn = "zonename"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@alt_adv"
param.DbType = Global.System.Data.DbType.[Object]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Blob
param.IsNullable = true
param.SourceColumn = "alt_adv"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@zoneid"
param.DbType = Global.System.Data.DbType.Int16
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int16
param.IsNullable = true
param.SourceColumn = "zoneid"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@instanceid"
param.DbType = Global.System.Data.DbType.UInt16
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt16
param.IsNullable = true
param.SourceColumn = "instanceid"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@pktime"
param.DbType = Global.System.Data.DbType.Int32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int32
param.IsNullable = true
param.SourceColumn = "pktime"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@inventory"
param.DbType = Global.System.Data.DbType.[Object]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Blob
param.IsNullable = true
param.SourceColumn = "inventory"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@groupid"
param.DbType = Global.System.Data.DbType.UInt32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt32
param.IsNullable = true
param.SourceColumn = "groupid"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@extprofile"
param.DbType = Global.System.Data.DbType.[Object]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Blob
param.IsNullable = true
param.SourceColumn = "extprofile"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@class"
param.DbType = Global.System.Data.DbType.[SByte]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.[Byte]
param.IsNullable = true
param.SourceColumn = "class"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@level"
param.DbType = Global.System.Data.DbType.UInt32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt24
param.IsNullable = true
param.SourceColumn = "level"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@lfp"
param.DbType = Global.System.Data.DbType.[Byte]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UByte
param.IsNullable = true
param.SourceColumn = "lfp"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@lfg"
param.DbType = Global.System.Data.DbType.[Byte]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UByte
param.IsNullable = true
param.SourceColumn = "lfg"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@mailkey"
param.DbType = Global.System.Data.DbType.StringFixedLength
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.[String]
param.IsNullable = true
param.SourceColumn = "mailkey"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_id"
param.DbType = Global.System.Data.DbType.Int32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int32
param.IsNullable = true
param.SourceColumn = "id"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_account_id"
param.DbType = Global.System.Data.DbType.Int32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int32
param.IsNullable = true
param.SourceColumn = "account_id"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_name"
param.DbType = Global.System.Data.DbType.[String]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.VarChar
param.IsNullable = true
param.SourceColumn = "name"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@IsNull_timelaston"
param.DbType = Global.System.Data.DbType.Int32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int32
param.IsNullable = true
param.SourceColumn = "timelaston"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
param.SourceColumnNullMapping = true
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_timelaston"
param.DbType = Global.System.Data.DbType.UInt32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt32
param.IsNullable = true
param.SourceColumn = "timelaston"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_x"
param.DbType = Global.System.Data.DbType.[Single]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Float
param.IsNullable = true
param.SourceColumn = "x"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_y"
param.DbType = Global.System.Data.DbType.[Single]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Float
param.IsNullable = true
param.SourceColumn = "y"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_z"
param.DbType = Global.System.Data.DbType.[Single]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Float
param.IsNullable = true
param.SourceColumn = "z"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_zonename"
param.DbType = Global.System.Data.DbType.[String]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.VarChar
param.IsNullable = true
param.SourceColumn = "zonename"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_zoneid"
param.DbType = Global.System.Data.DbType.Int16
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int16
param.IsNullable = true
param.SourceColumn = "zoneid"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_instanceid"
param.DbType = Global.System.Data.DbType.UInt16
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt16
param.IsNullable = true
param.SourceColumn = "instanceid"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_pktime"
param.DbType = Global.System.Data.DbType.Int32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.Int32
param.IsNullable = true
param.SourceColumn = "pktime"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_groupid"
param.DbType = Global.System.Data.DbType.UInt32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt32
param.IsNullable = true
param.SourceColumn = "groupid"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_class"
param.DbType = Global.System.Data.DbType.[SByte]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.[Byte]
param.IsNullable = true
param.SourceColumn = "class"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_level"
param.DbType = Global.System.Data.DbType.UInt32
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UInt24
param.IsNullable = true
param.SourceColumn = "level"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_lfp"
param.DbType = Global.System.Data.DbType.[Byte]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UByte
param.IsNullable = true
param.SourceColumn = "lfp"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_lfg"
param.DbType = Global.System.Data.DbType.[Byte]
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.UByte
param.IsNullable = true
param.SourceColumn = "lfg"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New Global.MySql.Data.MySqlClient.MySqlParameter()
param.ParameterName = "@Original_mailkey"
param.DbType = Global.System.Data.DbType.StringFixedLength
param.MySqlDbType = Global.MySql.Data.MySqlClient.MySqlDbType.[String]
param.IsNullable = true
param.SourceColumn = "mailkey"
param.SourceVersion = Global.System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Adding the attached code here solved the problem of the concurrency violation
ASKER
http://www.eggheadcafe.com/community/vb/14/10262903/concurrency-violation-the-update-command-affected-0-of-the-1-record.aspx
This table does have floats in them, 3 per row in fact. So I'm thinking that this may be part of the problem.
I tried 'updating' the float values so they would be changed as well,
Open in new window
and still get the concurrency error....
As the commands are auto-generated and I'm not using the command builder, what options do i have now?