Bug #4386 Inserted data doesn't update properly
Submitted: 2 Jul 2004 20:41 Modified: 7 Jul 2004 18:02
Reporter: Reggie Burnett Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version: OS:
Assigned to: Reggie Burnett CPU Architecture:Any

[2 Jul 2004 20:41] Reggie Burnett
Description:
I posted this to the ByteFx mailing list several days ago and got no 
answer. I'm hoping the someone on this list can help me.

I'm not certain if this is a bug or that I'm doing something wrong.

I'm getting an error when I try to update a datatable through a
dataadapter. I
only get the error when the datatable contains an inserted record. The
commandbuilder is generating an insertcommand and it looks valid. What
puzzles
me is that the error message reads: There is no Original data to access.
I
wouldn't think that it would need Original data when doing an Insert.
When I
copy the database over to MSSQL and use the SQL provider this code works.
I
need it to work on MySQL though.

Below are the specifics.

Language: VB.Net 2003
OS: Windows 2000
Server: MySQL 4.0.14-max-nt-log
Provider: ByteFx v 0.76

Error message:
System.Data.VersionNotFoundException: There is no Original data to
access.
    at System.Data.DataRow.GetOriginalRecordNo()
    at
System.Data.DataRow.GetRecordFromVersion(DataRowVersion version)
    at System.Data.DataRow.get_Item(DataColumn column,
DataRowVersion version)
    at System.Data.DataRow.get_Item(String columnName,
DataRowVersion version)
    at
ByteFX.Data.MySqlClient.MySqlCommandBuilder.SetParameterValues(MySqlCommand
cmd, DataRow dataRow)
    at
ByteFX.Data.MySqlClient.MySqlCommandBuilder.OnRowUpdating(Object 
sender,
MySqlRowUpdatingEventArgs args)
    at
ByteFX.Data.MySqlClient.MySqlDataAdapter.OnRowUpdating(RowUpdatingEventArgs
value)
    at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows,
DataTableMapping tableMapping)
    at System.Data.Common.DbDataAdapter.Update(DataSet
dataSet, String 
srcTable)
    at DataEntryLogic.DB_MySQL.UpdateTable(DataSet&
ds, String TableName) in
C:\Development\CSS7.0\Tools\CSS System
Manager\DataEntryLogic\DB_MySQL.vb:line
262

Table structure:
CREATE TABLE `sourcecode_master` (
   `AKey` int(11) NOT NULL auto_increment,
   `ClassName` varchar(100) NOT NULL default '',
   `FileName` varchar(100) NOT NULL default '',
   `FilePath` varchar(255) NOT NULL default '',
   `PathFileName` varchar(255) NOT NULL default '',
   `SourceSafeProject` varchar(255) NOT NULL default '',
   `OriginalAuthor` varchar(100) NOT NULL default '',
   `CreationDate` datetime default NULL,
   `ComponentTypeKey` int(11) NOT NULL default '0',
   `Purpose` varchar(255) NOT NULL default '',
   `ExternalRequirements` varchar(255) NOT NULL default '',
   `Notes` longtext NOT NULL,
   `VersionKey` int(11) NOT NULL default '0',
   PRIMARY KEY  (`AKey`),
   KEY `Version` (`VersionKey`),
   KEY `ComponentType` (`ComponentTypeKey`)
) TYPE=MyISAM;

Source query:
Select AKey, ClassName, FileName, FilePath, PathFileName,
SourceSafeProject,
OriginalAuthor, CreationDate, ComponentTypeKey, Purpose,
ExternalRequirements,
Notes, VersionKey FROM sourcecode_master Order by ClassName

Code:

Note: The database access code is abstracted into a separate class. The
following is the routine that I use to fill the dataset and to update it.
This
works fine for record updates but not inserts.

         Public Function
FillDataTable(ByVal SQL As String, ByVal TableName 
As String,
ByRef ds As DataSet) As DataTable Implements IDBAccess.FillDataTable
                
Try
                        
'Setup Command
                        
_cmd = New MySqlCommand(SQL, _cnn)

                        
'Fill the dataset
                        
Try
                                
_a = New MySqlDataAdapter
                                
_cb = New MySqlCommandBuilder
                                
_a.SelectCommand = New MySqlCommand(SQL, _cnn)
                                
_cb = New MySqlCommandBuilder(_a)

                                
'The docs say that the next 5 lines aren't 
necessary but I get
                                
'errors if I don't include them
                                
_a.DeleteCommand = _cb.GetDeleteCommand
                                
_a.InsertCommand = _cb.GetInsertCommand
                                
_a.UpdateCommand = _cb.GetUpdateCommand
                                
_a.FillSchema(ds, SchemaType.Source, 
TableName)
                                
_a.MissingSchemaAction = 
MissingSchemaAction.AddWithKey

                                
_a.Fill(ds, TableName)
                        
Catch ex As Exception
                                
MsgBox(ex.ToString)
                        
End Try
                        
Return ds.Tables(TableName)
                
Catch ex As Exception
                        
MsgBox(ex.ToString)
                        
Return Nothing
                
End Try
         End Function

         Sub UpdateTable(ByRef ds
As DataSet, ByVal TableName As String) 
Implements
IDBAccess.UpdateTable
                
Dim Affected As Integer
                
Try
                        
_a.MissingSchemaAction = 
MissingSchemaAction.AddWithKey

                        
If Not _cnn.State = ConnectionState.Open Then
                                
_cnn.Open()
                        
End If
                        
_a.InsertCommand.Connection = _cnn
                        
_a.DeleteCommand.Connection = _cnn
                        
_a.UpdateCommand.Connection = _cnn
                        
_a.SelectCommand.Connection = _cnn
                        
Affected = _a.Update(ds, TableName)
                
Catch ex As Exception
                        
MsgBox(ex.ToString)
                
End Try
         End Sub

Robert Rowe

Robert Rowe
Coal Software & Systems

Robert Rowe
Coal Software & Systems 

How to repeat:
not entered

Suggested fix:
not entered
[7 Jul 2004 18:02] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html