none
Saving Results of a Merged Dataset RRS feed

  • Question

  • Hi,

     

    I am trying to save the results of a merged datatable to my SQL Server.  The Loading/Merging functions are working fine.  I'm not sure what part

    of the code is messing up.  I'm unable to saved the merged results.  Here's the code:

    Imports System.Data.Common

    Public Class Main
       
    Dim dtOriginal As DataTable
       
    Dim dtMerged As DataTable

       
    Private Sub LoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadData.Click
           
    LoadDataMethod()
       
    End Sub

       
    Private Sub MergeData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MergeData.Click
           
    MergeDataMethod()
       
    End Sub

       
    Private Sub UpdateData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateData.Click
           
    UpdateDataMethod()
       
    End Sub

       
    Private Sub LoadDataMethod()

            dtOriginal
    = New DataTable()
            dtMerged
    = New DataTable()
            dtChanges
    = New DataTable()

           
    Dim connection As New SqlClient.SqlConnection("Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password")
           
    Dim adapter As New SqlClient.SqlDataAdapter("SELECT PersonID,Fname,Lname FROM Person", connection)

           
    Using adapter
                adapter
    .Fill(dtMerged)
                dtMerged
    .Constraints.Add("PKPersonID", dtMerged.Columns("PersonID"), True)
               
    MergedDataGrid.DataSource = dtMerged

           
    End Using

           
    Dim oleconnection As New OleDb.OleDbConnection("Provider=VFPOLEDB.1;Data Source='C:\DatabaseOriginal\'")
           
    Dim oleadapter As New OleDb.OleDbDataAdapter("SELECT Key as PersonID,SFirstName as Fname,SLastName as LName FROM custtable", oleconnection)

           
    Using oleadapter
                oleadapter
    .Fill(dtOriginal)
               
    OriginalDataGrid.DataSource = dtOriginal
           
    End Using

       
    End Sub

       
    Private Sub MergeDataMethod()

            dtMerged
    .Merge(dtOriginal, False, MissingSchemaAction.Ignore)
            dtMerged
    .AcceptChanges()
           
    MergedDataGrid.DataSource = dtMerged

       
    End Sub

       
    Private Sub UpdateDataMethod()

           
    Using connection As New SqlClient.SqlConnection("Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password")
               
    Dim adapter As New SqlClient.SqlDataAdapter("SELECT PersonID,Fname,Lname FROM PERSON", connection)
               
    Dim cb As New SqlClient.SqlCommandBuilder(adapter)
                cb
    .ConflictOption = ConflictOption.OverwriteChanges
                adapter
    .Update(dtChanges)
           
    End Using


       
    End Sub


    End Class

    • Moved by SamAgain Monday, April 19, 2010 1:56 AM better fit (From:.NET Base Class Library)
    Saturday, April 17, 2010 5:07 PM

Answers

  • The code in UpdateDataMethod() is having the adapter apply the updates from the dtChanges table, but from your sample, I don't see where that table gets populated or where changes are applied to it.

    If you meant to apply the updates in the merged DataSet, note that the call to AcceptChanges in MergeDataMethod makes the table "forget" that it has any changes pending.

    Monday, April 19, 2010 6:53 PM

All replies

  • The code in UpdateDataMethod() is having the adapter apply the updates from the dtChanges table, but from your sample, I don't see where that table gets populated or where changes are applied to it.

    If you meant to apply the updates in the merged DataSet, note that the call to AcceptChanges in MergeDataMethod makes the table "forget" that it has any changes pending.

    Monday, April 19, 2010 6:53 PM
  • What's the goal?  It sounds like you want to overwrite the data from your SQL Server table with the data in your Visual FoxPro table.  Is that accurate?  Do all of the rows in the VFP table already exist in the SQL Server table?
    David Sceppa
    Tuesday, April 20, 2010 4:26 AM
    Moderator