Manipulate tables in datagridview

[Simply Me]

Hi!I can retrieve data from my Table1 and display it in datagrid view. some of the Table1 fields are the same with Table2 fields. What I like is that whenever I make changes (add, edit, delete) to table1, the same changes will reflect to table2.

Basically, the code is already working for table1 only I added some codes to try it out if it will work for table2(the commented codes).

Public Class frmEditTbl
    Dim cnn As New SqlConnection
    Dim delete As New SqlCommand
    Dim insert As New SqlCommand
    Dim updates As New SqlCommand
    'Dim updates1 As New SqlCommand
    Dim adapter As New SqlDataAdapter
    'Dim adapter1 As New SqlDataAdapter
    'Dim builder As New SqlCommandBuilder(adapter)

    Dim dsEnrolFile As New DataSet
    Dim dsStudFile As New DataSet

    Private Sub frmEditProfile_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnn = New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=MYDB;Integrated Security=True")
        adapter = New SqlDataAdapter("SELECT DISTINCT (IDNO),LastName,Firstname, MiddleName,Gender,SectionName,Yearlevel FROM tbl1", cnn)
        'adapter1 = New SqlDataAdapter("SELECT DISTINCT (IDNO),LastName,Firstname, MiddleName,Gender FROM tbl2", cnn)

        delete = New SqlCommand("DELETE FROM tbl1 WHERE ID = @ID", cnn)
        insert = New SqlCommand("INSERT INTO tbl1 (IDNO, LastName, FirstName, MiddleName) VALUES (@IDNO, @LastName, @FirstName, @MiddleName)", cnn)
        updates = New SqlCommand("UPDATE tbl1 SET LastName=@LastName,FirstName=@FirstName,MiddleName=@MiddleName,Gender=@Gender,SectionName=@SectionName,YearLevel=@YearLevel WHERE IDNO = @IDNO", cnn)
        'updates = New SqlCommand("UPDATE tbl2 SET IDNO=@IDNO,LastName=@LastName,FirstName=@FirstName,MiddleName=@MiddleName,Gender=@Gender WHERE IDNO = @IDNO", cnn)
        delete.Parameters.Add("@ID", SqlDbType.NVarChar, 100, "IDNO")

        insert.Parameters.Add("@IDNO", SqlDbType.NVarChar, 10, "IDNO")
        insert.Parameters.Add("@FirstName", SqlDbType.NVarChar, 100, "FirstName")
        insert.Parameters.Add("@MiddleName", SqlDbType.NVarChar, 100, "MiddleName")
        insert.Parameters.Add("@LastName", SqlDbType.NVarChar, 100, "LastName")

        updates.Parameters.Add("@IDNO", SqlDbType.NVarChar, 100, "IDNO")
        updates.Parameters.Add("@FirstName", SqlDbType.NVarChar, 100, "FirstName")
        updates.Parameters.Add("@MiddleName", SqlDbType.NVarChar, 100, "MiddleName")
        updates.Parameters.Add("@LastName", SqlDbType.NVarChar, 100, "LastName")
        updates.Parameters.Add("@Gender", SqlDbType.NVarChar, 100, "Gender")
        updates.Parameters.Add("@SectionName", SqlDbType.NVarChar, 100, "SectionName")
        updates.Parameters.Add("@YearLevel", SqlDbType.NVarChar, 100, "YearLevel")

        adapter.DeleteCommand = delete
        adapter.InsertCommand = insert
        adapter.UpdateCommand = updates

        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Dim table As New DataTable
        'Retrieve the data.
        adapter.Fill(dsEnrolFile, "tbl1")
        'adapter1.Fill(dsStudFile, "tbl2")

        'bind datagrid view
        dtgEnrolFile.DataSource = dsEnrolFile.Tables("tbl1")

      End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'Save the changes.
        adapter.Update(dsEnrolFile.Tables("tbl1"))
        'adapter1.Update(dsStudFile.Tables("tbl2"))
        MessageBox.Show("Record Successfully Saved.", "SAVE")
    End Sub

Any help please?

[manhit45]

'i have finished solutions as case of you

'and if you want table1 reflect table2 ,so you can load them into

'datagridview1 and datagridview 2 then you can :



Dim khoxedt As New DataTable
Dim PTdataset As DataSet
Dim PTdataview As DataView

Dim objDataAdapterPT As New SqlDataAdapter("SELECT * from Kho_Xe ", objConnection)


PTdataset = New DataSet()
objDataAdapterPT.Fill(PTdataset, "PT")
objDataAdapterPT.Fill(PTdt)
PTdataview = New DataView(PTdataset.Tables("PT"))

datagridview1.DataSource = PTDataView
datagridview2.DataSource = PTDataView

'HOPE THAT HELPS

[Simply Me]

Thanks for the quick reply.

I am not going to display table2 in datagridview, only table1 is displayed in datagridview.

[manhit45]

A DataSet can contain a number of DataTable objects; when you use the SqlDataAdapter class ’ s
Fill method to add data to a DataSet , you are actually creating a DataTable object inside the
DataSet . The DataView provides a custom view of a DataTable ; you can sort or filter the rows, for
example, as you can in an SQL query.

So you use dataview you will make table1 and table2 reflect to it and get result.

[Simply Me]

you mean I have to change my current code?

[manhit45]

No,
you only collate to same dataview

[Simply Me]

any sample code?

[Simply Me]

Anyone here pls?