-
May 30th, 2008, 10:34 AM
#1
Thread Starter
Addicted Member
[RESOLVED] How do I enable datagridview to display related records?
I want to create a one-to-many data entry form using a dataadapter. I want to replicate (using dataadapter as opposed to tableadapter) what is happening in this msdn tutorial video called "Create a One-to-Many Form" -
http://msdn.microsoft.com/en-us/vbasic/bb643827.aspx
Scince starting to learn vb.net I have only worked with a single db table "tblcustomers" held in SQL Server. I have the connectionstring, commandstring, dataset all set up for the single table. I have never used a second table in vb.net for related data.
I have created a second table called "tblorders" in SQL Server with a primary key of "OrderID". OrderID will be a foreign key in my "tblcustomers" table. I have created a relationship between the two tables via the database diagram. So I have a one-to-many relationship - one customer can have many orders.
My question is - how do I go about displaying customers details in the textboxes, and their orders in the datagridview?
Here is a snippet of what I have, I havent included the stored proc calls here -
Code:
Dim conn As New SqlConnection
Dim dataAdapter As SqlDataAdapter
Dim ds As DataSet
Dim dataTable As DataTable
Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=False;" & "Initial Catalog=testdb;Data Source=MY-PC\SQLEXPRESS"
Dim commandString As String = "SELECT * from tblcustomers, tblorders"
Dim currRec As Integer = 0
Dim totalRec As Integer = 0
Dim insertSelected As Boolean
Private customersbindingsource As New BindingSource()
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conn = New SqlConnection(connectionString)
dataAdapter = New SqlDataAdapter(commandString, conn)
ds = New DataSet()
dataAdapter.Fill(ds, "tblcustomers")
dataTable = ds.Tables("tblcustomers")
Me.customersbindingsource.DataSource = ds.Tables("tblcustomers")
dgDetails.DataSource = customersbindingsource
Me.txtcustomerId.DataBindings.Add(New Binding("Text", _
Me.customersbindingsource, "customerId", True))
Me.txtArticleTitle.DataBindings.Add(New Binding("Text", _
Me.customersbindingsource, "title", True))
Me.txtArticleTopic.DataBindings.Add(New Binding("Text", _
Me.customersbindingsource, "topic", True))
Me.txtorderId.DataBindings.Add(New Binding("Text", _
Me.customersbindingsource, "orderId", True))
end sub
Private Sub btnNext_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnNext.Click
customersbindingsource.MoveNext()
End Sub
etc etc etc
Last edited by lithium1976; May 31st, 2008 at 03:44 AM.
-
May 30th, 2008, 12:44 PM
#2
-
May 30th, 2008, 01:34 PM
#3
Re: How do I enable datagridview to display related records?
If you did want to stick with the DataGridView instead of using a DataGrid though, I guess you could just use another binding source and use an SQL command like this to select all of the orders for the selected customer:
vb Code:
"SELECT * FROM tblOrders WHERE CustomerID = '" & txtCustomerID.Text & "'"
Assuming that you had a CustomerID field in the tblOrders table anyway... if you dont then obviously you would need a slightly different SQL command. In fact my entire post is probably pretty pointless cos I doubt you have a CustomerID field in both tables but meh it might help you a bit.
-
May 30th, 2008, 02:22 PM
#4
Thread Starter
Addicted Member
Re: How do I enable datagridview to display related records?
Yes I have 'customerid' in "tblorders" also. One customer can have many orders.
I just want it so the customers details appear in the textboxes, and the customers orders appear in the datagridview.
-
May 30th, 2008, 02:36 PM
#5
Re: How do I enable datagridview to display related records?
From the user's perspective that might appear a little odd. After they select a customer on the gird, the list of customers goes away and is replaced by the list of that customer's orders. What happens when they want to select another customer? How would they get the original list back?
The way I've usually seen a situation like this handled is by having one grid to select the customer and another grid to display the order details. If you're going to display the customer's details in textboxes, then you may not need a grid to select the customer with - a listview may do fine.
You can then use the selected customer's customerID to filter the customer table and display the detail in the textboxes, as well as filter grid displaying the orders table.
I always add to the reputation of those whose posts are helpful, and even occasionally to those whose posts aren't helpful but who obviously put forth a valiant effort. That is, when the system will allow it.
My war with a browser-redirect trojan
-
May 30th, 2008, 02:44 PM
#6
Thread Starter
Addicted Member
Re: How do I enable datagridview to display related records?
Originally Posted by dolot
From the user's perspective that might appear a little odd. After they select a customer on the gird, the list of customers goes away and is replaced by the list of that customer's orders. What happens when they want to select another customer? How would they get the original list back?
The way I've usually seen a situation like this handled is by having one grid to select the customer and another grid to display the order details. If you're going to display the customer's details in textboxes, then you may not need a grid to select the customer with - a listview may do fine.
You can then use the selected customer's customerID to filter the customer table and display the detail in the textboxes, as well as filter grid displaying the orders table.
No one will be selecting a customer on the grid. Maybe I havent explained clearly, appologies if I havent..
I want to replicate (using dataadapter as opposed to tableadapter) what is happening in this msdn tutorial video called "Create a One-to-Many Form" -
http://msdn.microsoft.com/en-us/vbasic/bb643827.aspx
Customers details shown in the textboxes, and their orders in the datagridview.
Last edited by lithium1976; May 31st, 2008 at 03:21 AM.
-
May 31st, 2008, 03:45 AM
#7
Thread Starter
Addicted Member
Re: How do I enable datagridview to display related records?
Any hints on how to set about it using a dataadapter? I have to admit it looks so easy in the MSDN video, but im trying to learn the programatic approach.
-
May 31st, 2008, 03:57 AM
#8
Re: How do I enable datagridview to display related records?
You should follow a couple of the links in my signature. The Data Access link will show you how to populate DataTables using a DataAdapter, amongst other things. The Master/Detail Data-binding link will show you how to bind to provide automatic filtering of related data.
-
May 31st, 2008, 04:10 AM
#9
Thread Starter
Addicted Member
Re: How do I enable datagridview to display related records?
Originally Posted by jmcilhinney
You should follow a couple of the links in my signature. The Data Access link will show you how to populate DataTables using a DataAdapter, amongst other things. The Master/Detail Data-binding link will show you how to bind to provide automatic filtering of related data.
I have already populated my datatable using the dataadapter, just for the tblcustomers though. I have also set up the stored procedures etc all which work for that single table, i didnt include them in the code snippet in this thread. I will check out the mast/detail link next.
Last edited by lithium1976; May 31st, 2008 at 04:13 AM.
-
Jun 1st, 2008, 04:18 AM
#10
Thread Starter
Addicted Member
Re: How do I enable datagridview to display related records?
Ok I have done it..
Set up bindingsource, connection string, dataset etc. Add data from both tables -
Code:
Dim masterDataAdapter As New SqlDataAdapter("select * from Customers", connection)
masterDataAdapter.Fill(data, "Customers")
Dim detailsDataAdapter As New SqlDataAdapter("select * from Orders", connection)
detailsDataAdapter.Fill(data, "Orders")
Establish a relation between the two tables -
Code:
Dim relation As New DataRelation("CustomersOrders", _
data.Tables("Customers").Columns("CustomerID"), _
data.Tables("Orders").Columns("CustomerID"))
data.Relations.Add(relation)
Then bind everything -
Code:
' Bind the master data connector to the Customers table.
masterBindingSource.DataSource = data
masterBindingSource.DataMember = "Customers"
detailsBindingSource.DataSource = masterBindingSource
detailsBindingSource.DataMember = "CustomersOrders"
'bind textbox to the masterbindingsource, add more textboxes if needed -
Me.txtcustomerId.DataBindings.Add(New Binding("Text", _
Me.masterBindingSource, "customerId", True))
'If using two datagridviews instead of texboxes/datagridview, use masterdatagridview code below -
'masterdatagridview.DataSource = masterBindingSource
detailsDataGridView.DataSource = detailsBindingSource
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|