Binding 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 at the same time 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")

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 appreciated

Thanks

Answer this question

Binding to a datagrid

vbvan
Thanks for your help
Szymon Kobalczyk
there may be a bug in ListBox1_SelectedIndexChange event handler.It seems it fires twice. check this out on the web before you do any further coding.
regards
steve
garak0410
Simon,

Are you using one grid or two You can do either depending on how you want it to look.

The first (or only) grid should be bound either like this:

Me.oGrid.DataSource = MyDataSet.Tables(0)

or this:

Me.oGrid.DataSource = MyDataSet.Relations("MyRelation").ParentTable

(Either syntax produces the same results)

You'll notice when you run this, the Grid will have little "+" signs on the left and clicking them will expand and show "MyRelation". Click on that and you get all the child rows.

Alternatively, you can bind a second grid to the relation and it will show the child rows as you click through the parent rows in the first grid. You bind the second grid like this:

Me.oGrid2.DataSource is the same as the parent grid, but you include a DataMember also:

Me.oGrid2.DataMember = "MyRelation"

If you use two grids and you don't want the "+" to show in the parent grid, set it's AllowNavigation property to false.
JFTxJ
You're welcome, Simon. Always glad to help.
Asif Iqbal
Simon,

Sorry I forgot that you said you were choosing the Customer from a ListBox, so forget what I was saying about two grids. You'd only have one grid and it would be bound to the Orders relationship (as I showed in my second example). So, something like this:

Me.oGrid.DataSource = MyDataSet.Tables("Customer")
Me.oGrid.DataMember = "OrderRelation"

As far as bringing up Dialog forms when double-clicking on a row in the DataGrid, you'll have to mess with that one yourself (I don't use Grids much and I have no sample code for how to do this, but I 'm sure it's just a matter of utilizing an event handler for some grid event). For an excellent and comprehensive how-to article on Grids, check out Kevin Goff's article in CoDe magazine at http://www.code-magazine.com/Article.aspx quickid=0411081
coddy

Thanks again for the reply

Tried that, there's no problem in showing the orders for all customer id's in the datagrid but what I'm trying to do is display the orders placed for each selected customer id only

For example if I select customer ID 2, then the info is displayed in the 5 textboxes which works fine then for that particular customer id I need to show all the orders placed in the datagrid for that ID only then if I pick Customer ID 4, the info is displayed in the textboxes and the order details only for that selected customer id are shown in the datagrid, the OrderID, OrderPrice and CustomerID to be shown in the datagrid are from the Orders Table

Also if I double click a row on the daatgrid I need to show a dialog consisting of another datagrid which displays the order details for the selected order on the first screen which is from the Items Table

I'm new to this kind of programming so your help has been great so far so any tips/coding on the above would be appreciated if possible

Thank-you
niceguysfinishlast
Have your DataSet contain both tables and set up a relationship between parent (Customer) and child (OrderDetails). Then use the relationship as the DataSource for your DataGrid.
RameshPa

Thanks for the reply, have tried to create a data relation but still can't get it to work, my code is the following

Dim myRelation as New DataRelation("Name of data relation, column1. column2)
ds.Relations.Add(myRelation)

My dataset does contain both tables it's just the info for the OrderID, OrderPrice and CustomerID won't load into the datagrid for each selected customerid

Any tips/coding to help with this would be great if possible

Thanks
Taniabr
I wouldn't call it a bug. It's called twice because when you already have an Item in the ListBox selected, and then you select another one, the first time the SelectedIndexChanged event fires is because it's *de-selecting* the item (so, SelectedIndices.Count will be zero). Then it fires again, because the item you clicked on is now selected.

So, all you need to do in your SelectedIndexChanged event handler is have a check for SelectedIndices.Count != 0
Binding to a datagrid

Answer this question