Update CE database from DataTables -- AdapterUpdate?

Hi everyone.

VS 2008, SQL CE, Windows Mobile 6 Pro device, me (quite new to all this)

I have a CE database on a mobile device and want to deploy a form through which a user can add, edit, or delete records from two tables on the CE database. (Because there are two tables in the original database, I have not been using dbCommandBuilders because I can only use it if my DataTable maps to a single database table, correct?)

The table I want to update in the CE database is called SiteLocation; fields are Greenspace and pkSiteID. When I change the values of the Greenspace and pkSiteID form controls, I can see those new values in the DataTable currently visible in the form, but nothing seems to change in the CE database itself (and I ultimately don't want the DataTable on the form). No apparent errors in the code, though.

Disclaimer: I have adapted much of this code from MSDN as I'm quite new to this. Any help is appreciated.

Imports System.Data
Imports System.Data.SqlServerCe

...

    Private Sub AdapterUpdate(ByVal connectionString As String)

        Using sqlCEConn As SqlCeConnection = New SqlCeConnection(connectionString)

            Dim adapter As SqlCeDataAdapter = New SqlCeDataAdapter( _
              "SELECT pkSiteID, Greenspace FROM table.SiteLocation", _
              sqlCEConn)

            adapter.UpdateCommand = New SqlCeCommand( _
              "UPDATE table.SiteLocation SET Greenspace = @Greenspace " & _
               "WHERE pkSiteID = @pkSiteID", sqlCEConn)

            adapter.UpdateCommand.Parameters.Add( _
            "@Greenspace", SqlDbType.NVarChar, 30, "Greenspace")

            Dim parameter As SqlCeParameter = _
               adapter.UpdateCommand.Parameters.Add( _
               "@pkSiteID", SqlDbType.Int)
            parameter.SourceColumn = "pkSiteID"
            parameter.SourceVersion = DataRowVersion.Original

            Dim SiteLocationTable As New DataTable
            Dim SiteLocationRow As DataRow = SiteLocationTable.Rows(0)
            adapter.Fill(SiteLocationTable)

            adapter.Update(SiteLocationTable)

            Console.WriteLine("Rows after update.")
            Dim row As DataRow
            For Each row In SiteLocationTable.Rows
                Console.WriteLine("{0}: {1}", row(0), row(1))
            Next
        End Using
    End Sub
RJT_VTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
Not sure I fully understand your problem as you mentioned multiple tables but then only dealing with single table in code. The way it works is that you fill the table, keeping the adapter alive, and then call the Update command on adapter. Adapter will then pick up the changes and propagate to DB.
0
RJT_VTAuthor Commented:
My form will ultimately end up updating to 2 tables in the same database, but I was just dealing with the SiteLocation table to make sure I fully nailed down the update pathway. I have indeed filled the DataTable (as below). How do I 'keep the adapter alive' as you say. Was I not doing that in the original code?

    Private Sub DataEntry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If VCTI_SQL_CE_DataSetUtil.DesignerUtil.IsRunTime Then
            Me.InventoryTableAdapter.Fill(Me.VCTI_SQL_CE_DataSet.Inventory)
        End If
        If VCTI_SQL_CE_DataSetUtil.DesignerUtil.IsRunTime Then
            Me.SiteLocationTableAdapter.Fill(Me.VCTI_SQL_CE_DataSet.SiteLocation)
        End If
    End Sub
0
CodeCruiserCommented:
Declare the adapter at class level and do not dispose it after filling the table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RJT_VTAuthor Commented:
Thanks, CodeCruiser. I'm going to work through this and will report back asap.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Smartphone Programming

From novice to tech pro — start learning today.