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)
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:
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.
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
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")
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
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.
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
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...
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.
Reply With Quote
You should databind everything to your dataset
Bookmarks