Copy datasource from one DataTable to another in VB.Net 2005

Posted on 2006-06-01
Last Modified: 2010-08-05
Hi Experts,

My Test project includes the following:

"frmBids" = Parent form
"frmBidsDetail" = Child Datagridview

"frmBidImport" = Popup Datagridview with existing data in table "tblBidImport"

I need help on copying the data from "frmBidImport" datatable to the Child Datagridview "frmBidsDetail" datatable and updating to the database.

I have tried this several different ways. I get the data to the Child Datagridview OK, but I can't get the Child to synchronize with the Parent for updating.

A simple walkthrough would sure help as I am a beginner.



Question by:dave_sky
    LVL 3

    Expert Comment

    I suppose what you are trying to do is have a dataset which reads data from a database to be the data source of both the grids on the parent and child forms, and have another dataset, which reads data from another table with the same structure as the child table, for data import in the import form. If that's the case, you may consider getting rid of the import form as it's not necessary. You can use the same dataset for both the parent form and the detail form, and when the user modifies data on the child grid, the changes will be stored into the dataset automatically. Then, you just need to accept the changes or roll 'em back depending on which button the user clicks.

    As a beginner, if you don't know what I am talking about, you may post the codes of all three forms up here together with the explanation of your database, and I'll have a look for you.

    Hope this helps.

    Author Comment


    That's a good idea, I will post the codes of all three forms:

    Public Class frmBids

        Private Sub TblBidsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblBidsBindingNavigatorSaveItem.Click

            Dim deletedtblBidsDetail As BulkCopySQLDataSet.tblBidsDetailDataTable = CType( _
                BulkCopySQLDataSet.tblBidsDetail.GetChanges(Data.DataRowState.Deleted), BulkCopySQLDataSet.tblBidsDetailDataTable)

            Dim newtblBidsDetail As BulkCopySQLDataSet.tblBidsDetailDataTable = CType( _
                BulkCopySQLDataSet.tblBidsDetail.GetChanges(Data.DataRowState.Added), BulkCopySQLDataSet.tblBidsDetailDataTable)

            Dim modifiedtblBidsDetail As BulkCopySQLDataSet.tblBidsDetailDataTable = CType( _
                BulkCopySQLDataSet.tblBidsDetail.GetChanges(Data.DataRowState.Modified), BulkCopySQLDataSet.tblBidsDetailDataTable)

                ' Remove all deleted tblBidsDetail from the tblBidsDetail table.
                If Not deletedtblBidsDetail Is Nothing Then
                End If

                ' Update the Bids table.

                ' Add new BidsDetail to the BidsDetail table.
                If Not newtblBidsDetail Is Nothing Then
                End If

                ' Update all modified BidsDetail.
                If Not modifiedtblBidsDetail Is Nothing Then
                End If


            Catch ex As Exception
                MsgBox("Update failed")

                If Not deletedtblBidsDetail Is Nothing Then
                End If

                If Not newtblBidsDetail Is Nothing Then
                End If

                If Not modifiedtblBidsDetail Is Nothing Then
                End If
            End Try

        End Sub

        Private Sub frmBids_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'BulkCopySQLDataSet.tblBidsDetail' table. You can move, or remove it, as needed.
            'TODO: This line of code loads data into the 'BulkCopySQLDataSet.tblBids' table. You can move, or remove it, as needed.

        End Sub

        Private Sub btnBidsImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBidsImport.Click
        End Sub

    End Class

    Public Class frmBidImport

        Private Sub TblBidImportBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblBidImportBindingNavigatorSaveItem.Click

        End Sub

        Private Sub frmBidImport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'BulkCopySQLDataSet.tblBidImport' table. You can move, or remove it, as needed.

        End Sub
    End Class

    Author Comment

    The SQL Table "tblBidImport" is loaded in SQL Server by importing an Excel file.

    This table will have complete new data in it once per month.

    I need the data to be copied to the Parent/Child for recording since the previous months "tblBidImport" data will be erased or overwritten.

    I hope this is more clear.

    LVL 7

    Expert Comment

    Dave: Rather than doing this in VB.Net, you should look at using SQL-Servers Data Transformation Services, its ***much*** easier
    to set up and automate.

    In Enterprise Manager: Go to "Tools" | "Data Transformation Services" | "Import" and follow the steps provided.  When you are done, save the package
    and choose a schedule to automate the task.  You can even get DTS to do archiving tasks like copying or moving the Excel file to a backup folder
    when you are done or sending an email if the import fails

    LVL 3

    Accepted Solution

    Hi Dave,

    I agree with Bill that doing the loading by DTS is much easier. Yet, if you don't have DTS, or still want to use VB.Net for whatever reason, you don't need to retreive the deleted, new and changed records and update them separately like that, nor use another data set for Exdata import. Here are what you may do.

    1. Create one and only one data set and use it to retrieve data at the beginning of the application. You might make a module and put this data set into there or make it a shared member of the first form or whatever you wanna do as long as you use only one data set. Then, you use this data set to be the data sources of both the master and the detail forms. Indeed, it's also a good idea to have only one form showing both master and detail grids.
    2. You don't need an import form. Just have an "Import from Excel" button for the user to choose. Once they click that button, data from an Excel spreadsheet is then imported into the data set. Then, you can have "Accept" and "Reject" buttons for the user to click. If they cick "Accept", then you invoke the AcceptChanges method of the data set. If they click "Reject", then you invoke the RejectChanges method before using your data adapter to update the whole data set.

    Hope this is clear enough.

    Author Comment


    Regarding the comment from Bill:
    I am using SQL 2005 and it supports SSIS instead of DTS, and I also have to use VB.Net.

    Regarding the comment from Yokkui:
    I have my application configured like your have explained in Point 1.

    You have definitely helped me with the Excel info in Point 2.




    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    mirrorEnds challenge 6 64
    sameEnds challenge 25 55
    factory design pattern vs abstract factoy design pattern 2 61
    count7 challenge 12 52
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now