Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update CE database from DataTables -- AdapterUpdate?

Posted on 2012-04-05
4
Medium Priority
?
407 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 1500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

824 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