• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1073
  • Last Modified:

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

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.

Thanks,

Dave

0
dave_sky
Asked:
dave_sky
  • 3
  • 2
1 Solution
 
yokkuiCommented:
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.
0
 
dave_skyAuthor Commented:
Hi,

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
        Me.Validate()
        Me.TblBidsBindingSource.EndEdit()
        Me.TblBidsDetailBindingSource.EndEdit()

        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)

        Try
            ' Remove all deleted tblBidsDetail from the tblBidsDetail table.
            If Not deletedtblBidsDetail Is Nothing Then
                TblBidsDetailTableAdapter.Update(deletedtblBidsDetail)
            End If

            ' Update the Bids table.
            TblBidsTableAdapter.Update(BulkCopySQLDataSet.tblBids)

            ' Add new BidsDetail to the BidsDetail table.
            If Not newtblBidsDetail Is Nothing Then
                TblBidsDetailTableAdapter.Update(newtblBidsDetail)
            End If

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

            BulkCopySQLDataSet.AcceptChanges()

        Catch ex As Exception
            MsgBox("Update failed")

        Finally
            If Not deletedtblBidsDetail Is Nothing Then
                deletedtblBidsDetail.Dispose()
            End If

            If Not newtblBidsDetail Is Nothing Then
                newtblBidsDetail.Dispose()
            End If

            If Not modifiedtblBidsDetail Is Nothing Then
                modifiedtblBidsDetail.Dispose()
            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.
        Me.TblBidsDetailTableAdapter.Fill(Me.BulkCopySQLDataSet.tblBidsDetail)
        'TODO: This line of code loads data into the 'BulkCopySQLDataSet.tblBids' table. You can move, or remove it, as needed.
        Me.TblBidsTableAdapter.Fill(Me.BulkCopySQLDataSet.tblBids)

    End Sub

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

End Class

===================================================
Public Class frmBidImport

    Private Sub TblBidImportBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblBidImportBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TblBidImportBindingSource.EndEdit()
        Me.TblBidImportTableAdapter.Update(Me.BulkCopySQLDataSet.tblBidImport)

    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.
        Me.TblBidImportTableAdapter.Fill(Me.BulkCopySQLDataSet.tblBidImport)

    End Sub
End Class
0
 
dave_skyAuthor Commented:
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.

Dave
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Cheers,
-Bill
0
 
yokkuiCommented:
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.
0
 
dave_skyAuthor Commented:
Hi,

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.

Thanks,

Dave






0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now