SQLCommandBuilder how to load data from a DataTable

Hi,

I'm not sure what i'm doing wrong in the SQLCommandBuilder to get my data to load to SQL Server.

I'm pasting all the code, the LoadDataMethod, MergeDataMethod both work fine.  However, for the life of me i can't see what i'm doing wrong in the UpdateDataMethod.

Thanks!
Imports System.Data.Common

Public Class Main
    Dim dtOriginal As DataTable = New DataTable
    Dim dtMerged As DataTable = New 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.Clear()
        dtMerged.Clear()

        Dim stringOriginalData As New DbConnectionStringBuilder
        Dim stringMergedData As New DbConnectionStringBuilder

        With stringOriginalData
            .Add("Provider", "VFPOLEDB.1")
            .Add("Data Source", "C:\OrginalData.DBF")
        End With

        With stringMergedData
            .Add("Data Source", "myserver")
            .Add("Initial Catalog", "database")
            .Add("Persist Security Info", "True")
            .Add("User ID", "user")
            .Add("Password", "password")
        End With

        Dim connectionOriginalData As String = stringOriginalData.ConnectionString
        Dim conOriginal As New OleDb.OleDbConnection(connectionOriginalData)
        Dim connectionMergedData As String = stringMergedData.ConnectionString
        Dim conMerged As New SqlClient.SqlConnection(connectionMergedData)


        Using conOriginal
            conOriginal.Open()

            Using conMerged
                conMerged.Open()

                Dim daOriginal As New OleDb.OleDbDataAdapter("SELECT Key as PersonID,SFirstName as Fname,SLastName as Lname FROM FDCUSTDT", conOriginal)
                Dim daMerged As New SqlClient.SqlDataAdapter("SELECT PersonID,Fname,Lname FROM PERSON", conMerged)

                daOriginal.Fill(dtOriginal)
                daMerged.Fill(dtMerged)
                dtMerged.PrimaryKey = New DataColumn() {dtMerged.Columns("PersonID")}

                OriginalDataGrid.DataSource = dtOriginal
                MergedDataGrid.DataSource = dtMerged

                conMerged.Close()
            End Using

            conOriginal.Close()
        End Using

    End Sub

    Private Sub MergeDataMethod()

        dtMerged.Merge(dtOriginal)
        MergedDataGrid.DataSource = dtMerged

    End Sub

    Private Sub UpdateDataMethod()

        Dim stringMergedData As New DbConnectionStringBuilder


        With stringMergedData
            .Add("Data Source", "myserver")
            .Add("Initial Catalog", "database")
            .Add("Persist Security Info", "True")
            .Add("User ID", "user")
            .Add("Password", "password")
        End With

        Dim connectionMergedData As String = stringMergedData.ConnectionString
        Using conMerged As New SqlClient.SqlConnection(connectionMergedData)
            conMerged.Open()
            Dim daMerged As New SqlClient.SqlDataAdapter("SELECT PersonID,Fname,Lname FROM PERSON", conMerged)
            Dim cbMerged As New SqlClient.SqlCommandBuilder(daMerged)
            cbMerged.ConflictOption = ConflictOption.OverwriteChanges
            daMerged.Update(dtMerged)
            conMerged.Close()
        End Using

    End Sub


End Class

Open in new window

todd_nakasatoAsked:
Who is Participating?
 
grayeCommented:
It looks like you're recreating the daMerged several times...  You should create it just once at the beginning of your application.  That way when you use the CommandBuilder, you're actually using the same DataAdapter instance... and not a new one.
0
 
GRayLCommented:
Line 79:  Dim stringMergedData As New DbConnectionStringBuilder - I don't think DbConnectionStringBuilder is a datatype

0
 
todd_nakasatoAuthor Commented:
Hi GrayL:

It is a datatype, i believe its found in System.Data.Common.  But that part of the program is working fine.  I really didn't need to use the DbConnectionStringBuilder.  I really am still stuck on getting a merged dataset to save back to the server.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GRayLCommented:
Sorry, I am not familiar with SQLCommandBuilder - I should not have stuck my nose in.
0
 
Melih SARICAOwnerCommented:
any errors ? did u check that any pending changes in ur dataset  ?
0
 
CodeCruiserCommented:
Try inserting records manually by using command objects.
0
 
todd_nakasatoAuthor Commented:
Hi, I am able (using the datagrid) to add records manually and append to the sql server.  However,
when i run the mergeddatamethod i can get those records to save.  Any sample code on .AcceptChanges()?
0
 
CodeCruiserCommented:
If you visualize the merged dataset, do you see any problems?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.