Solved

Update CE database from DataTables -- AdapterUpdate?

Posted on 2012-04-05
4
376 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

832 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