J
John Tamburo
vs 2005, vb, .net 2.0, using sql 2005 Compact edition, compiling for desktop
use.
I retrieve a dataset with 0-1 rows of a table. Table is a single table with
an identity column as primary key. Identity column is selected into the
dataset. In this case we're retrieving 0 rows, and adding a row. Form
populated with databound controls (textboxes only for this test). Bindings
work perfectly.
I pass dataset to my object to post up the collected data. Data row 0 of
table 0 shows perfectly, just as typed. Primary key (identity is on) is
null. Code executes without exceptions, the update call you see below
returns 1 for one row.
However, the only non-null column in the whole table is the primary key
itself. Table name in the dataset matches the table name in the sql-ce
database. I am stumped. The values are in the dataset. the rowstate is 4
(added). I see this when I watch the code, both before and after the update.
HELLLP! There is no viable option to hand-code the commands and parameters.
I put in an event handler for rowupdated event of the adapter. the
parameters show the correct column mappings but they are all value of
system.dbnull.
I am stumped and suspect that this is a MAJOR NASTY BUG in the
commandbuilder or the dataadapter.
Here is the code:
Public Function PostDataSet( _
ByRef dsResult As System.Data.DataSet, _
ByRef iRet As Integer, _
ByRef sErr As String) _
As Boolean
Dim dcConn As New SqlCeConnection(sConnect)
Try
dcConn.Open()
Catch ex As Exception
sErr = "Error in running PostDataSet, DB=" & sActiveDB & ",
Message=" & ex.Message
dcConn.Dispose()
Return False
End Try
Dim cbcmd As SqlCeCommand = dcConn.CreateCommand()
cbcmd.CommandText = "select * from " & dsResult.Tables(0).TableName
Dim cb As New SqlCeCommandBuilder()
Dim daAdapter As New SqlCeDataAdapter(cbcmd)
cb.DataAdapter = daAdapter
cb.SetAllValues = True
With daAdapter
Try
iRet = .Update(dsResult, dsResult.Tables(0).TableName)
Catch ex As Exception
sErr = "Cannot update the " & dsResult.Tables(0).TableName &
" table, error=" & ex.Message
dcConn.Close()
dcConn.Dispose()
Return False
End Try
If iRet < 1 Then
sErr = "Nothing was updated."
dcConn.Close()
dcConn.Dispose()
Return False
End If
End With
dcConn.Close()
dcConn.Dispose()
sErr = ""
Return True
End Function
use.
I retrieve a dataset with 0-1 rows of a table. Table is a single table with
an identity column as primary key. Identity column is selected into the
dataset. In this case we're retrieving 0 rows, and adding a row. Form
populated with databound controls (textboxes only for this test). Bindings
work perfectly.
I pass dataset to my object to post up the collected data. Data row 0 of
table 0 shows perfectly, just as typed. Primary key (identity is on) is
null. Code executes without exceptions, the update call you see below
returns 1 for one row.
However, the only non-null column in the whole table is the primary key
itself. Table name in the dataset matches the table name in the sql-ce
database. I am stumped. The values are in the dataset. the rowstate is 4
(added). I see this when I watch the code, both before and after the update.
HELLLP! There is no viable option to hand-code the commands and parameters.
I put in an event handler for rowupdated event of the adapter. the
parameters show the correct column mappings but they are all value of
system.dbnull.
I am stumped and suspect that this is a MAJOR NASTY BUG in the
commandbuilder or the dataadapter.
Here is the code:
Public Function PostDataSet( _
ByRef dsResult As System.Data.DataSet, _
ByRef iRet As Integer, _
ByRef sErr As String) _
As Boolean
Dim dcConn As New SqlCeConnection(sConnect)
Try
dcConn.Open()
Catch ex As Exception
sErr = "Error in running PostDataSet, DB=" & sActiveDB & ",
Message=" & ex.Message
dcConn.Dispose()
Return False
End Try
Dim cbcmd As SqlCeCommand = dcConn.CreateCommand()
cbcmd.CommandText = "select * from " & dsResult.Tables(0).TableName
Dim cb As New SqlCeCommandBuilder()
Dim daAdapter As New SqlCeDataAdapter(cbcmd)
cb.DataAdapter = daAdapter
cb.SetAllValues = True
With daAdapter
Try
iRet = .Update(dsResult, dsResult.Tables(0).TableName)
Catch ex As Exception
sErr = "Cannot update the " & dsResult.Tables(0).TableName &
" table, error=" & ex.Message
dcConn.Close()
dcConn.Dispose()
Return False
End Try
If iRet < 1 Then
sErr = "Nothing was updated."
dcConn.Close()
dcConn.Dispose()
Return False
End If
End With
dcConn.Close()
dcConn.Dispose()
sErr = ""
Return True
End Function