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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
GRayLCommented:
Sorry, I am not familiar with SQLCommandBuilder - I should not have stuck my nose in.
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.