Solved

Update CE database from DataTables -- AdapterUpdate?

Posted on 2012-04-05
4
359 Views
Last Modified: 2013-12-27
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
0
Comment
Question by:RJT_VT
  • 2
  • 2
4 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37816658
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
 

Author Comment

by:RJT_VT
ID: 37816681
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 37816847
Declare the adapter at class level and do not dispose it after filling the table.
0
 

Author Comment

by:RJT_VT
ID: 37817213
Thanks, CodeCruiser. I'm going to work through this and will report back asap.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now