Databinding to a datagrid


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Databinding to a datagrid

  1. #1
    Join Date
    Aug 2005
    Posts
    8

    Databinding to a datagrid

    Can anyone help with this one

    Have a form with a listbox and 5 textboxes, by clicking the different customer id's in the listbox this then displays the relevant info in the textboxes which is first name, last name, city, state and zip code, this works fine but I'm trying to display the order details for each selected order id in a datagrid but can't get it to work

    The order details to be displayed in the datagrid from the database are OrderID, OrderPrice and CustomerID, do I need to incorporate a data relation here

    This is the coding I have so far

    Imports System.Data.OleDb
    Imports System.IO

    Public Class Form1
    Inherits System.Windows.Forms.Form


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Declares the command object
    Dim Sql As String = "SELECT * FROM customers"

    'Declares the connection object
    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\The Doughnut Shop.mdb"
    'Declares and instantiates a new OleDbConnection object
    Dim myConnection As OleDbConnection = New OleDbConnection(connString)
    myConnection.ConnectionString = connString
    'Declares and instantiates a new data adapter
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(Sql, myConnection)

    'Declares and instantiates a new data set
    Dim ds As DataSet = New DataSet
    'Fills the data set
    da.Fill(ds, "customers")

    Dim recordNum As Integer

    recordNum = ds.Tables("customers").Rows.Count - 1

    Dim i As Integer
    'Loops through the data set and adds the information accordingly
    For i = 0 To recordNum
    ListBox1.Items.Add(ds.Tables("customers").Rows(i).Item("Customerid"))
    Next
    TextBox1.Text = ds.Tables("customers").Rows(0).Item("FirstName")
    TextBox2.Text = ds.Tables("customers").Rows(0).Item("LastName")
    TextBox3.Text = ds.Tables("customers").Rows(0).Item("City")
    TextBox4.Text = ds.Tables("customers").Rows(0).Item("State")
    TextBox5.Text = ds.Tables("customers").Rows(0).Item("ZipCode")
    DataGrid1.DataSource = ds.

    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
    Dim Sql As String = "SELECT * FROM customers"
    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\The Doughnut Shop.mdb"
    Dim myConnection As OleDbConnection = New OleDbConnection(connString)
    myConnection.ConnectionString = connString
    Dim ds As New DataSet
    Dim da As New OleDbDataAdapter(Sql, myConnection)

    myConnection.ConnectionString = connString

    da.Fill(ds, "customers")

    Dim id As Integer
    id = ListBox1.Text

    TextBox1.Text = ds.Tables("customers").Rows(id - 1).Item("FirstName")
    TextBox2.Text = ds.Tables("customers").Rows(id - 1).Item("LastName")
    TextBox3.Text = ds.Tables("customers").Rows(id - 1).Item("City")
    TextBox4.Text = ds.Tables("customers").Rows(id - 1).Item("State")
    TextBox5.Text = ds.Tables("customers").Rows(id - 1).Item("ZipCode")

    End Sub


    End Class

    Any coding/advice would be great

    Thanks

  2. #2
    Join Date
    Aug 2005
    Posts
    1

    datagrid

    You have to bind the listitem to datagrid.
    datagrid.databind()

  3. #3
    Join Date
    Aug 2005
    Posts
    22

    Talking You should databind everything to your dataset

    By databinding all of your form elements to your datasource - in this case a dataset, you can easily change all of the textboxes by making a selection with your listbox. Similarly, you will also be able to create a relation between your two tables and when you make a selection in your listbox, you will be able to determine the appropriate orders very easily and use this information to populate a datagrid/etc.

    To get some idea of what I am talking about, try this:

    Code:
    'Binding listbox to dataset
    ListBox1.DataSource = ds.Tables("customers")
    ListBox1.DisplayMember = "Customerid"
    ListBox1.ValueMember = "Customerid"
    
    'Binding TextBox to dataset
    TextBox1.DataBindings.Add(new Binding("Text", ds.Tables("customers"), "LastName"))
    This should give you some idea of what I am talking about. Try using this code just to see how it works. By changing the selection in the listbox a change will also be made in the textbox. I hope it works - I am translating this from C#.

    I have an excellent example of exactly what you are looking for. I would like to put together an example for you. I will do so very soon. I have to run right now, but I will put it together and post it very soon.

  4. #4
    Join Date
    Aug 2005
    Posts
    8
    Thanks for the reply, certainly looks easier doing it that way, have altered my coding but for some reason the program won't load, any ideas, I'm pressing F5 to run but keeps coming up in debug mode, I'm choosing to stop debugging but get the same thing again when I try to run it again here's my amended coding

    Imports System.Data.OleDb
    Imports System.IO

    Public Class Form1
    Inherits System.Windows.Forms.Form


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Declares the command object
    Dim Sql As String = "SELECT * FROM customers"

    'Declares the connection object
    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\The Doughnut Shop.mdb"

    'Declares and instantiates a new OleDbConnection object
    Dim myConnection As OleDbConnection = New OleDbConnection(connString)
    myConnection.ConnectionString = connString

    'Declares and instantiates a new data adapter
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(Sql, myConnection)

    'Declares and instantiates a new data set
    Dim ds As DataSet = New DataSet

    'Fills the data set
    da.Fill(ds, "customers")

    ListBox1.DataSource = ds.Tables("customers")
    ListBox1.DisplayMember = "customerid"
    ListBox1.ValueMember = "customerid"

    TextBox1.DataBindings.Add(New Binding("text", ds.Tables("customers"), "Firstname"))
    TextBox2.DataBindings.Add(New Binding("text", ds.Tables("customers"), "Lastname"))
    TextBox3.DataBindings.Add(New Binding("text", ds.Tables("customers"), "City"))
    TextBox4.DataBindings.Add(New Binding("text", ds.Tables("customers"), "State"))
    TextBox5.DataBindings.Add(New Binding("text", ds.Tables("customers"), "Zip Code"))





    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
    Dim Sql As String = "SELECT * FROM customers"
    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\The Doughnut Shop.mdb"
    Dim myConnection As OleDbConnection = New OleDbConnection(connString)
    myConnection.ConnectionString = connString
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(Sql, myConnection)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, "customers")


    ListBox1.DataSource = ds.Tables("customers")
    ListBox1.DisplayMember = "customerid"
    ListBox1.ValueMember = "customerid"

    TextBox1.DataBindings.Add(New Binding("text", ds.Tables("customers"), "Firstname"))
    TextBox2.DataBindings.Add(New Binding("text", ds.Tables("customers"), "Lastname"))
    TextBox3.DataBindings.Add(New Binding("text", ds.Tables("customers"), "City"))
    TextBox4.DataBindings.Add(New Binding("text", ds.Tables("customers"), "State"))
    TextBox5.DataBindings.Add(New Binding("text", ds.Tables("customers"), "Zip Code"))

    Have I missed something out here for the program not to load at all, any tips/coding would be great also that program you mentioned might be a great help too

    End Sub


    End Class

  5. #5
    Join Date
    Aug 2005
    Posts
    22

    Talking I'm working on it...

    I haven't forgotten about you! I am working on the program right now and will have it done fairly soon. As for your loading problems, are you getting any error messages? Are any exceptions being thrown? You could start by commenting out those lines/statements that you recently included and see if you can get it to work. This could at least help you start to isolate the source of the problem.

    The program is coming...

  6. #6
    Join Date
    Aug 2005
    Posts
    8
    Thanks

    Have tried commenting out certain code but it's weird as it tries to run the program then stops and it allows me to press F5 again surely the program should either run fine, run then get a run-time error or doesn't run at all due to an exception, must be something simple, trying to get it to work as the coding you've done seems more neater and tidier

    Cheers again for your help

  7. #7
    Join Date
    Aug 2005
    Posts
    22

    Talking Here it is...

    This thing seems to have taken on a life of it's own...

    This example includes three classes that can all be placed in one file - just copy, paste, compile, and run.

    All of the data is created dynamically - it is created purely in the program. This is done in the subroutines CreateTables() and PopulateTables(). They create a Customer and an Orders table and populate each with sample data. These tables are added to dataSet1. A third table is created with the same columns as Orders but it is placed in dataSet2 - it will hold the list of orders that are associated with the currently selected customer. The data in dataSet2 is what will be displayed in the dataGrid. The data in dataSet2 Orders table is the result of a relation created between the Customerid (as the parent) in the Customer table and the Customerid (the child) in the Orders table - this relation is created in CreateRelations(). This way many orders can be associated with a single customer.

    The customerid column in the Customer table is bound to the listBox in the upper left of the main form. The textboxes in the upper right are bound to the other columns in the Customer table. All databinding is done in BindDataSet(). When a user selects a customerid from the listbox two things happen. First, as a result of databinding, all of the other customer data (first name, last name, city, state, etc) for the selected customer are displayed in the textboxes in the upper right. Second, based on the customerid, the subroutine GetOrders() retrieves all of the orders for that customer - this makes use of the parent/child relation we created earlier. The order information is placed in dataSet2 Orders table. The dataGrid has been bound to this table. Consequently, the dataGird will display the information for all of the orders for the selected customer.

    Information for the customer can be changed via the textboxes and the changes - as a result of databinding - will automatically be reflected in the Customer table of dataSet1. Changes concering Orders can be made by right-clicking the dataGrid and making the appropriate selection (insert, remove, update). If inserting or updating an order, a second form will pop up - the form is defined in OrderForm.MainFrom which is declared near the bottom of the code. It allows the user to enter a price for either the new order or a new price for an already existing order. The customerid that is displayed in the pop-up form is that of the currently selected customer in the listbox/textfields. The orderid shown is the next sequential orderid from the Orders table of dataSet1. The class OrderForm.OrderInfo simply facilitates passing data back and forth from the main form and the pop-up form. All of the business logic relating to inserting, removing, and updating of orders can be seen in InsertRow(), RemoveRow(), adn UpdateRow().

    I think that with the databinding, etc. you will find this approach to be much more streamlined than the apporach you were taking. Also, by not frequently accessing your database you will incur far less overhead.

    That's all I can think of right now. I wll include all of the code below. Again, just copy, paste, compile, and run. Let me know if this helps or if it needs any further explanation/clarification.

    Actually, the text for the program is too long for this reply - the form won't allow me to post it. I will attach a txt copy of it to this reply... Let me know if it comes through properly and works for you...
    Attached Files Attached Files
    Last edited by str_test; 09-05-2005 at 04:15 PM.

  8. #8
    Join Date
    Aug 2005
    Posts
    22

    Talking Another version with parent, child, grandchild...

    Someone in another forum was having problems displaying a datagrid that shows a parent - child - grandchild relationship in one datagrid. I have added another table ("OrderDescr" - order descriptions) along with another relationship to the program attached to the posting above. Now dataset1 has tables: Customers, Orders, and OrdersDescr with relations (parent, child, grandchild) in this same order. The datagrid it is bound to is at the bottom - by clicking the "+" you will be able to see the relationships. It really does not do much except demonstrate this three-tiered relationship. I will attach a txt copy of it to this post. Copy, paste, compile, and run. Look at the above posting and the comments in the program for a more complete description of what is happening.
    Attached Files Attached Files

Similar Threads

  1. Replies: 5
    Last Post: 05-04-2005, 01:37 PM
  2. Replies: 0
    Last Post: 06-05-2003, 10:30 PM
  3. ASP.NET - Update database with databinding
    By MichaelChoi in forum ASP.NET
    Replies: 2
    Last Post: 01-15-2003, 08:37 PM
  4. Is there guidelines for proper use of DataGrid
    By Thomas Eyde in forum .NET
    Replies: 8
    Last Post: 08-01-2002, 07:43 PM
  5. Threading and the .NET Datagrid
    By McCleud in forum .NET
    Replies: 2
    Last Post: 08-01-2002, 11:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center