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()
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()