SQLCE DataAdpater.Update says updated but hasn't

[Guest]

Hi,

I have an application running on a Windows Mobile 5.0 device and am using
the following code to update tables in a SQL Server Mobile database . For
some tables, the following code works fine but for others the database does
not get updated. The Update method returns the number of records that should
be updated, the RowUpdating event fires but the RowUpdated event does not and
there are no errors thrown.

The tables passed to the sub do not originate from the database, instead
they have been constructed using data from an external source. The tables do
not contain any primary key information.

Filling the datatable from the dataadapter after the update shows that no
changes have been made to the database.

private sub UpdateDatabase(sTable as String, dtTable as DataTable)

' daTable is declared at module level WithEvents
daTable = New SqlCeDataAdapter("SELECT * FROM " & sTable, ssceconn)

With New SqlCeCommandBuilder(daTable)
daTable.InsertCommand = .GetInsertCommand()
daTable.UpdateCommand = .GetUpdateCommand()
daTable.DeleteCommand = .GetDeleteCommand()
End With

daTable.Update(dtTable)

daTable.Dispose()

end sub

I am using VS2005, VB.NET, SQL CE 3.0 and CF 2.0.

 

[William \(Bill\) Vaughn]

There should be no need to "populate" the action commands (UpdateCommand
etc...) before executing the Update--unless you're not sure what the
CommandBuilder is constructing. When the Update is first executed and a CB
has been linked in, the action commands are generated and the action
commands are populated behind the scenes. I would talk a look at what's
being built but if it looks right, take out the Get... commands.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Hi Bill,

I commented out the lines to populate the action commands and the result is
the following: System.InvalidOperationException: Update requires a valid
InsertCommand when passed DataRow collection with new rows.

 

[Kevin Yu [MSFT]]

Hi Mike,

I think this is by design that you get an InvalidOperationException. Since
you mentioned that the tables do not contain any primary key information.

We can see in the document of SqlCeCommandBuilder class that primary key
column is required in the SelectCommand for generating the other statements.

For more information, please check the following link:

http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecomman
dbuilder.aspx

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Hi Kevin,

I have modified the code as below but it still fails to update properly. All
the tables in the database have primary keys and I have tried adding primary
keys to the dtTable but get the same problem.

I have done some further tests and have found that Update works for new
records, modified records cause the DataAdapter.RowUpdating event to fire and
for DataAdapter.Update to return the correct number of records that should
have been updated but the database is not updated, and deleted records cause
a DBConcurrencyException.

Does DataAdpater.Update(DataTable) require that DataTable is filled using a
DataAdapter, or does it allow you to construct the DataTable and load it with
data yourself?




Private Sub UpdateDatabase(ByVal sTable As String, ByVal dtTable As DataTable)

daTable = New SqlCeDataAdapter("SELECT * FROM " & sTable, ssceconn)

Dim cb As New SqlCeCommandBuilder
cb.DataAdapter = daTable

daTable.Update(dtTable)

End Sub

 

[Kevin Yu [MSFT]]

Hi Mike,

Yes, DataAdapter allows youto construct the DataTable and load data
yourself.

If this is the case, I suggest you check the update and delete statement in
the RowUpdating event. In the event handler, you can use e.Command to check
the exact command and its parameters. Also please check e.TableMapping to
see if the parameters are mapping to correct columns in the DataTable.

I also suggest you generate the DataSet schema from the DataAdapter. It
will make sure the schema matches the result DataAdapter generates.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

Hi Kevin,

After a bit of research on the CommandBuilder I have found what the problem
is. Basically, if you load the data into the DataTable yourself and the
original values of the data don't match those in the database then the Update
and Delete commands generated by the CommandBuilder wont work. You have to
construct your own.

Check out the following Microsoft article for an explanation why:

http://msdn2.microsoft.com/en-us/library/tf579hcz(d=ide).aspx

 

[Kevin Yu [MSFT]]

Hi Mike,

Yes, this is exactly what I meant in my last post. It was nice to know that
you have had the problem resolved. Thanks for sharing your experience with
all the people here. If you have any questions, please feel free to post
them in the community.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)