Solved

VB.NET MySQL Concurrency Violation

Posted on 2012-03-21
3
1,109 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…

730 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