Manipulate tables in datagridview
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).
Code:
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?
Re: Manipulate tables in datagridview
'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:afrog:
Re: Manipulate tables in datagridview
Thanks for the quick reply.
I am not going to display table2 in datagridview, only table1 is displayed in datagridview.
Re: Manipulate tables in datagridview
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.
Re: Manipulate tables in datagridview
you mean I have to change my current code?
Re: Manipulate tables in datagridview
No,
you only collate to same dataview
:rolleyes:
Re: Manipulate tables in datagridview
Re: Manipulate tables in datagridview