Solved

Update CE database from DataTables -- AdapterUpdate?

Posted on 2012-04-05
4
393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 setup several different housekeeping processes for a SQL Server.

752 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