Bug #5798 DBNull Values causing problems with retrieving/updating queries.
Submitted: 29 Sep 2004 11:00 Modified: 22 Oct 2004 20:00
Reporter: Barry Zubel Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:1.0.0 (bk-2004-09-28) OS:Windows (XP SP2)
Assigned to: Reggie Burnett CPU Architecture:Any

[29 Sep 2004 11:00] Barry Zubel
Description:
Retrieved the latest bk build (2004-09-28).

Upon compilation, and use, there appears to be a change from 1.0.0(release) in the handling of certain field types.

When retrieving a resultset from the table defined below, any "empty" (but NOT "NULL") varchar fields return a value of DBNull instead of a blank string.

Conversely, if attempting to update the same table, the Update command fails with  Concurrency Error (0 records to update).  This appears to be connected to the commenting out of some code around line 320 in Commandbuilder.cs

As it is now impossible to use the current bk source to commit an update to the database, I've ranked this high/critical.  However, if there has been a procedural change between 1.0.0 and bk-2004-09-28 then please advise!

Thanks.

How to repeat:
Run the following code against the following database table.

--- TABLE DEFINITION ---

CREATE TABLE `s_master` (
  `ID` int(11) NOT NULL auto_increment,
  `JOB_NO` int(7) NOT NULL default '0',
  `EN_NO` int(7) NOT NULL default '0',
  `C_NO` smallint(6) NOT NULL default '0',
  `SQTY` int(8) NOT NULL default '0',
  `CUSTONO` varchar(20) NOT NULL default '',
  `ISSUED` date default NULL,
  `REQD` date default NULL,
  `INVQTY` int(11) NOT NULL default '0',
  `INVOICED` date default NULL,
  `UNITPRICE` double NOT NULL default '0',
  `ORDVAL` double NOT NULL default '0',
  `INVVAL` double NOT NULL default '0',
  `VAT` double NOT NULL default '0',
  `VATRATE` double NOT NULL default '0',
  `TOOLVAL` double NOT NULL default '0',
  `OTHERVAL` double NOT NULL default '0',
  `OTHERNAR` varchar(30) NOT NULL default '',
  `EXVAT` double NOT NULL default '0',
  `INCVAT` double NOT NULL default '0',
  `TONNES` double NOT NULL default '0',
  `DELQTY` int(8) NOT NULL default '0',
  `DELREASON` varchar(80) NOT NULL default '',
  `DELIVERD` date default NULL,
  `PALLETS` smallint(6) NOT NULL default '0',
  `DPRIORITY` char(1) NOT NULL default '',
  `REP` smallint(6) NOT NULL default '0',
  `ESTVAL` double NOT NULL default '0',
  `MATVAL` double NOT NULL default '0',
  `DQ` smallint(6) NOT NULL default '0',
  `EXPORTED` int(1) NOT NULL default '0',
  `Owner` varchar(4) NOT NULL default '',
  `DNOTES` varchar(255) NOT NULL default '',
  `PROFORMA` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `jobno` (`JOB_NO`),
  KEY `c_no` (`C_NO`),
  KEY `custono` (`CUSTONO`),
  KEY `deliverd` (`DELIVERD`),
  KEY `dpriority` (`DPRIORITY`),
  KEY `en_no` (`EN_NO`),
  KEY `ennojob` (`EN_NO`,`JOB_NO`),
  KEY `invoiced` (`INVOICED`),
  KEY `issued` (`ISSUED`),
  KEY `rep` (`REP`),
  KEY `repcu` (`REP`,`C_NO`),
  KEY `reqd` (`REQD`),
  KEY `EXPORTED` (`EXPORTED`)
) TYPE=InnoDB;
    
-- CODE USED ---
        Dim conn As New MySqlConnection(APDApp.MySql.ConnectionString)
        conn.Open()
        Dim trans As MySqlTransaction = conn.BeginTransaction(IsolationLevel.Serializable)
        Dim comm As New MySqlCommand
        Dim rs As New MySqlDataAdapter(comm)
        Dim dt As New DataTable
        Dim bNew As Boolean
        Dim sSql As String
        sSql = "SELECT * FROM S_MASTER WHERE JOB_NO=" & ord.JobNo

        comm.Connection = conn
        comm.Transaction = trans
        comm.CommandText = sSql
        Dim du As New MySqlCommandBuilder(rs)
        Try
            rs.Fill(dt)

            Dim row As DataRow
            If dt.Rows.Count = 0 Then
                bNew = True
                row = dt.NewRow()
            Else
                bNew = False
                row = dt.Rows(0)
            End If
            row("JOB_NO") = ord.JobNo
            row("C_NO") = ord.CNO
            row("EN_NO") = ord.EnNo
            row("SQTY") = ord.SQty
            row("CUSTONO") = ord.CustONo
            row("ISSUED") = ord.Issued
            row("REQD") = ord.Reqd
            If ord.Invoiced = Nothing Then
                row("INVOICED") = System.DBNull.Value
            Else
                row("INVOICED") = ord.Invoiced
            End If
            row("UNITPRICE") = ord.UnitPrice
            row("ORDVAL") = Math.Round(ord.OrdVal, 2)
            row("INVVAL") = Math.Round(ord.InvVal, 2)
            row("INVQTY") = Math.Round(ord.InvQty, 2)
            row("VAT") = Math.Round(ord.VAT, 2)
            row("VATRATE") = ord.VatRate
            row("TOOLVAL") = ord.ToolVal
            row("OTHERVAL") = ord.OtherVal
            row("OTHERNAR") = ord.OtherNarr
            row("EXVAT") = Math.Round(ord.ExVat, 2)
            row("INCVAT") = Math.Round(ord.IncVat, 2)
            row("TONNES") = ord.Tonnes
            row("DELQTY") = ord.DelQty
            row("DELREASON") = ord.DelReason
            If ord.Delivered = Nothing Then
                row("DELIVERD") = DBNull.Value
            Else
                row("DELIVERD") = ord.Delivered
            End If
            row("PALLETS") = ord.pallets
            row("DPRIORITY") = ord.DelPriority
            row("REP") = ord.Rep
            row("ESTVAL") = ord.EstVal
            row("MATVAL") = ord.MatVal
            row("DQ") = ord.DQ
            row("OWNER") = ord.Owner.ID
            row("DNOTES") = ord.DNotes
            row("PROFORMA") = ord.ProForma
            If ord.Exported Then
                row("EXPORTED") = 1
            Else
                row("EXPORTED") = 0
            End If

            If bNew Then
                dt.Rows.Add(row)
            End If

            rs.Update(dt)   <--- FAILS WITH DBCONCURRENCY ERROR

            dt.Dispose()
            rs.Dispose()
            comm.Dispose()
            du.Dispose()

 Catch ex As Exception
            MsgBox("Error saving order: " & vbCrLf & ex.ToString)
            trans.Rollback()
        End Try

        conn.Close()
[22 Oct 2004 20:00] 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

Additional info:

I was not able to reproduce this with the current code base but I marked it fixed in source code.