Results 1 to 10 of 10

Thread: [RESOLVED] How do I enable datagridview to display related records?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2008
    Location
    United Kingdom
    Posts
    168

    Resolved [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.

  2. #2
    Frenzied Member dolot's Avatar
    Join Date
    Nov 2007
    Location
    Music city, U.S.A.
    Posts
    1,253

    Re: How do I enable datagridview to display related records?


    My understanding is that this is one function the datagrid possesses that the datagridview does not. You could probably fake it in some bizarre complicated way, but using the datagrid is much better. As long as your two tables are contained in a dataset and joined in a primary key/foreign key relationship the grid can handle the 'drill down' from one table to the next automatically.

    Now don't ask me how to control the formatting of the columns in the child table because I wouldn't have a clue.
    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

  3. #3
    Pro Grammar chris128's Avatar
    Join Date
    Jun 2007
    Location
    England
    Posts
    7,604

    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:
    1. "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.
    My free .NET Windows API library (Version 2.2 Released 12/06/2011)

    Blog: cjwdev.wordpress.com
    Web: www.cjwdev.co.uk


  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jan 2008
    Location
    United Kingdom
    Posts
    168

    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.

  5. #5
    Frenzied Member dolot's Avatar
    Join Date
    Nov 2007
    Location
    Music city, U.S.A.
    Posts
    1,253

    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

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jan 2008
    Location
    United Kingdom
    Posts
    168

    Re: How do I enable datagridview to display related records?

    Quote 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.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2008
    Location
    United Kingdom
    Posts
    168

    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.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jan 2008
    Location
    United Kingdom
    Posts
    168

    Re: How do I enable datagridview to display related records?

    Quote 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.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jan 2008
    Location
    United Kingdom
    Posts
    168

    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
  •  



Click Here to Expand Forum to Full Width