SQLCeCommandBuilder...

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


.

Relevant Pages

  • Re: Propogating Scope Identity from "INSTEAD OF INSERT" Trigger
    ... the reliable way is to use the primary key to retrieve the ... For clarification. ... IDENTITY column is probably defined as the PK. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help inserting specific value into an identity column
    ... I have tried to modify the code to take the insert command that SqlCommandBuilder generates and modify it to include the identity column in both the column list and the values list. ... Dim lconTest As New SqlConnection ... Dim ldaTest As New SqlDataAdapter("select insertID, insertText from insertTest", lconTest) ...
    (microsoft.public.dotnet.framework.adonet)
  • Help inserting specific value into an identity column
    ... I have tried to modify the code to take the insert command that SqlCommandBuilder generates and modify it to include the identity column in both the column list and the values list. ... Dim lconTest As New SqlConnection ... Dim ldaTest As New SqlDataAdapter("select insertID, insertText from insertTest", lconTest) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Concurrency issue
    ... An IDENTITY column is one technique, and perhaps the most simple one. ... only potential downside is that after an INSERT operation you usually end up ... I have also heard of people using random numbers for the primary key column ...
    (microsoft.public.sqlserver.programming)
  • Re: identity and clustered indexes
    ... There is no surrogate primary key. ... having the identity column declared as clustered index ... address1 varcharnot null, ... declare @msg varchar ...
    (microsoft.public.sqlserver.server)
Loading