Solved

VB.NET MySQL Concurrency Violation

Posted on 2012-03-21
3
1,036 Views
Last Modified: 2012-08-13
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

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

Open in new window


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

Open in new window


Now filling the table ...

  MytableAdapter.FillByID(MyTable,"3")

Open in new window


Getting the data I need to work with

  RowData = DirectCast(MyTable.Select("id='3'")(0),MyTable.Table1Row)

Open in new window


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

Open in new window


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

Open in new window



MyTableAdapter.Update(MyTable)

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;

Open in new window


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)

Open in new window

0
Comment
Question by:sgaggerj
  • 3
3 Comments
 
LVL 1

Author Comment

by:sgaggerj
ID: 37748971
I've been doing some more searching while waiting for help and came across this

http://www.eggheadcafe.com/community/vb/14/10262903/concurrency-violation-the-update-command-affected-0-of-the-1-record.aspx

If the database record contains a floating point value (even if that floating point value is not being changed) then this exception can be generated due to a rounding error. For example the value 5.6 cannot be stored without rounding the database (or in memory either, but will be represented by the approximation 5.599999...) To complicate matters slight hardware differences between the implementation of floating point calculations on CPUs can mean that for identical data this error may be thrown on one system but not on another - which means that this error can arise in production and yet even with identical data cannot be reproduced in test.

If you are confident that this is the problem (and there is no risk of genuine concurrent updates to the data), then tell the driver to overwrite when it detects a "conflict", e.g.:

dim cb as OdbcCommandBuilder(adapter)
cb.ConflictOption = ConflictOption.OverwriteChanges

I have only encountered this specific issue when using the OdbcDataAdapter and OdbcCommandBuilder, but believe this same error can affect non-ODBC versions.

This approach can be used to avoid the error being thrown when there are genuine concurrent data updates, but I would not advise it.

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,

RowData.x = RowData.x
RowData.y = RowData.y
RowData.z = RowData.z

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?
0
 
LVL 1

Accepted Solution

by:
sgaggerj earned 0 total points
ID: 37749117
Well, it appears that I've solved it!

I added this to my DataSet extender...

Public Property UpdateCommand() As MySqlCommand
            Get
                Dim cmd As New MySqlCommand
                cmd.Connection = Me.Connection

                cmd.CommandType = CommandType.Text
                Dim param As Global.MySql.Data.MySqlClient.MySqlParameter = Nothing

                cmd.Connection = Me.Connection
                cmd.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 `timelaston` 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_instanceid) AND (`pktime` = @Original_pktime) AND (`groupid` = @Original_groupid) AND (`class` = @Original_class) AND (`level` = @Original_level) AND (`lfp` = @Original_lfp) AND (`lfg` = @Original_lfg) AND (`mailkey` = @Original_mailkey))"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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"
                cmd.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
                cmd.Parameters.Add(param)
                Return cmd
            End Get
            Set(value As MySqlCommand)
                Me.Adapter.UpdateCommand = value
            End Set
        End Property

Public Overloads Function Update(ByVal dataTable As MyDataSet.Table1, ByVal dummy As Integer) As Integer
            If Me.Adapter.UpdateCommand Is Nothing Then
                Me.Adapter.UpdateCommand = UpdateCommand
                Me.Adapter.UpdateCommand.Connection = Me.Connection
            End If

            Dim cmd As MySqlCommand = Me.Adapter.UpdateCommand
            Try
                Me.Adapter.UpdateCommand = UpdateCommand
                Return Adapter.Update(dataTable)
            Catch ex As Exception
                Return -1

            Finally
                Me.Adapter.UpdateCommand = cmd

            End Try


        End Function

Open in new window


Which essentially trimmed out all the extra 'original' parameters except for id, which will never change.

Now running

If MyTableAdapter.Update(MyTable, 0) > 0 Then

Open in new window


no longer gives a concurrency error, updates correctly, and I don't need to 'update' the rowdata.x,y,z
0
 
LVL 1

Author Closing Comment

by:sgaggerj
ID: 37749124
Adding the attached code here solved the problem of the concurrency violation
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now