LCP2000

Hi guys, I am trying to figure out how to get the info from a listbox to populate a datagridview when you click on a listbox item. I have four separate tables in an access db:

tbl_customers tbl_dishes tbl_wines tbl_deserts
-------------- ---------- --------- -----------
cust_id 1------------- ~ dishes_id 1------------ ~ wine_id 1------------- ~ desert_id
pete rose fishnchips classicwhite icecreams
johny depp lobster vignoles pastries
bill gates Wink burgers merlot fruits
Note: Relationship of tables is: 1 to many (
1--- ~).

Now, I have been able to populate my listbox with the customer names. But I cannot seem to populate the datagridview with the related tables by clicking on the customer name in the listbox.

I would appreciate any help/insight you all might have.
Thanks in advance,
LCP



Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

It's not tally clear what you trying to relate and a clear problem statement is always helpful.

You don't have tables with information about the relationships. Right now you just have "lookup tables"

I don't think you want a one to many table. I think you want a many to many table that would like like this:

EntreeRelationships
RecordNum CustomerID EntreeID
1 Bill Gates Burgers
2 Bill Gates Lobsters
3 Melissa Etheridge Steaks

with such a table, you can select Bill gates get his Customer ID in the customer table and then in the many to many table you look for all bill gates and find burger and Lobsters and then you can consult your entree information table for particulars.

I think you're going to have to SQL commands and "joins" to do this efficiently and there are some good references beginning here:

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx#acintsql_adovdao

http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx#acintsql_adovdao

I hope this helps........






Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Thanks for the reply, that db was an example (probably a bad one ). Let me try to be a bit more clear with a better example. Say I got two tables:


tbl_Owners
ID Owners_Name
1
Bill Gates
2
John Wayne
3
Melissa Etheridge

tbl_Pets
ID
Owners_ID Pets_Name
1
1
Fido
2
2
Cujo
3
1
Bambi
4
3
Killer

I populate my ListBox with the Owners Name, I want to be able to click on the owners name and have a DGV list all the pets owned, say, by Bill Gates.

I've gone as far as populating the ListBox but I am having trouble with the DGV.

Oh yea, btw, here is my sql too:
SELECT tbl_Owners.ID AS tbl_Owners_ID, tbl_Owners.Owners_Name, tbl_Pets.ID AS tbl_Pets_ID, tbl_Pets.Owner_ID, tbl_Pets.Pets_Name
FROM tbl_Owners INNER JOIN tbl_Pets ON tbl_Owners.[ID] = tbl_Pets.[Owner_ID];


Thanks for your help, hope this is clearer.
LCP





Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

How are you filling the datagridview

Does the output of the Select statement actually produce a datatable with the data columns you want If so, the table can be equated with the datagridview datasource property and it should display the view you want to see.

This is a good example. Either the select statement is not producing the desired table OR you aren't connecting it to the DGV correctly. I would test the select statement by production of a datatable where you can examine the rows and columns of your results.

By the way, you're really doing a good job...... in your questions and your clarifications. I'm sure many people here appreciate that.






Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Well this is what I've got so far, but I get a "Type mismatch in expression" error at my_DataAdapter.Fill(my_DataSet, "tbl_Owners").

Thanks for your help.
LCP


Code Snippet
Dim my_DataAdapter As New OleDbDataAdapter("SELECT tbl_Owners.ID AS tbl_Owners_ID, tbl_Owners.Owners_Name, tbl_Pets.ID AS tbl_Pets_ID, tbl_Pets.Owner_ID, tbl_Pets.Pets_Name FROM tbl_Owners INNER JOIN tbl_Pets ON tbl_Owners.[ID] = tbl_Pets.[Owner_ID];", objConnection)
Dim my_DataSet As DataSet
Dim my_DataView As DataView
Dim my_CurrencyManager As CurrencyManager

ListBox1.SelectedItems.Clear()

' Initialize a new instance of the DataSet object and
' fill the DataSet object with data...
my_DataSet = New DataSet()
my_DataAdapter.Fill(my_DataSet, "tbl_Owners")
my_DataView = New DataView(my_DataSet.Tables("tbl_Owners"))
my_CurrencyManager = CType(Me.BindingContext(my_DataView), CurrencyManager)

' Fill the DataSet object with data...
Me.ListBox1.DataSource = my_DataSet.Tables(0)
Me.ListBox1.DisplayMember = "tbl_Owners"
Me.ListBox1.ValueMember = "Owners_Name"

DataGridView1.DataSource = my_DataView







Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Ok, so I've got it working to the point where both controls get populated at the same time.
This is not what I want. I need the LB to populate first, then when an item on the LB is clicked,
the DGV should populate with the relevant data.

I've done this so far, but it doesn't work:

Code Snippet

Private Sub ListBox1_SelectedIndexChanged _
(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ListBox1.SelectedIndexChanged
DataGridView1.DataSource = ListBox1.SelectedIndex
End Sub


Please help, I'm so close...
LCP





Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

I can't tell you the number of technical problems I had to solve to make this response.

At work today, suddenly I could no longer respond to you. I don't know if it was the board or our intranet. THEN I arrived home, received a couple of email messages and the DSL went out. It's not due to be repaired until Thursday.

Soooooooo, I connected a high gain antenna to my wireless card and enabled it. NOW I have a connection. I'm sure you have the problem fixed by now. But here is what I saw with your last question:

DataGridView1.DataSource = ListBox1.SelectedIndex

...will never work because it's an item index into the listbox and not a datasource. It points to a row in your database.

I'd do something along this line.... but with your select statement and not mine.:

Dim result As New DataTable(tableName) ¡® Tablename is a string containing the

¡® table name

¡® You have a valid oledbconnection object

dim Adapter as OleDbDataAdapter

Using Adapter = New OleDbDataAdapter("Select * from [" & tableName & "]" & OrderString, con)

Try

Adapter.Fill(result)

result.TableName = tableName

Return result

Catch e As Exception

¡® error processing here

If result IsNot Nothing Then

result.Dispose()

result = Nothing

End If

Return Nothing

End If

End Using

Datagridview1.datasource = result







Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Sorry to here of your problems, hope you get them resolved soon ...

I appreciate your time on this code, but being a n00b, i seem to be at a loss.
If I put the code in a sub I get an error with "using adapter".
I seem to also have errors with "try", "using", "catch e" and "return"

Not sure but should there be a "end try" or "catch" hmmm...

I'll keep trying to figure it out.

Thanks again,
LCP





Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

You're quite the impressive beginner........

I'm sorry... my code was meant as model to suggest how it should be done in principal.

Using statements have an End Using

I added that and a couple of other things to my example which may make life easier.






Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Great, thanks. I seem to have it working part of the way. How can I get the listbox items to
populate the DGV, am i supposed to somehow reference the listbox items somewhere in this
code. Is it possible to make the listbox the datasource for the dgv

Sorry, I seem to be having a hard time with this.


Thanks again,
LCP





Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

I need a little more in the way of specifics. I'm going to run to the store for a moment. Please do two things for me while I'm gone.

please supply your table designs in those wonderful access tables and also

exactly what you want. You see, I don't even kknow what you really have in the listbox.... so it's difficult to assist you.






Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

I need a little more in the way of specifics. I'm going to run to the store for a moment. Please do two things for me while I'm gone.

please supply your table designs in those wonderful access tables and also

exactly what you want. You see, I don't even kknow what you really have in the listbox.... so it's difficult to assist you.

I never use listboxes, I use commboboxes.

A selected index is an index in the item collection so I think you can say

dim PersonID as integer = index

Then in your Select statement do this:

Dim my_DataAdapter As New OleDbDataAdapter("SELECT tbl_Owners.ID AS tbl_Owners_ID, tbl_Owners.Owners_Name, tbl_Pets.ID AS tbl_Pets_ID, tbl_Pets.Owner_ID, tbl_Pets.Pets_Name FROM tbl_Owners INNER JOIN tbl_Pets ON tbl_Owners.[ID] = tbl_Pets.[Owner_ID];", objConnection)

Hmmmmm the index MAY be equal to the OwnerID if you¡¯ve been clever.

So you can do this¡­ if I¡¯m reading the Select statement correctly:

Dim my_DataAdapter As New OleDbDataAdapter("SELECT tbl_Owners.ID AS tbl_Owners_ID, tbl_Owners.Owners_Name, tbl_Pets.ID AS tbl_Pets_ID, tbl_Pets.Owner_ID, tbl_Pets.Pets_Name FROM tbl_Owners INNER JOIN tbl_Pets ON tbl_Owners.[ID] = tbl_Pets.[¡° +cstr(index)+¡±];", objConnection)

BUT that¡¯s wrong too because you don¡¯t have a where clause.

Consider this Select statement:

SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name

Instead store names, you are interested in Someones ID

You¡¯ll need to do a little syntax dance when you finally get it together

To test it consider this last part and use it as a model:

WHERE A1.PetID = ¡°¡¯cstr(PersonID)¡¯¡±

NOW I'm going to the store !!! Big Smile








Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Thanks for the reply, these are the two tables I am working with now:


tbl_Owners
ID Owners_Name
1
Bill Gates
2
John Wayne
3
Melissa Etheridge

tbl_Pets
ID
Owners_ID Pets_Name
1
1
Fido
2
2
Cujo
3
1
Bambi
4
3
Killer


I have a listbox which gets populated with tbl_Owners.Owners_Name.

ListBox
Bill Gates
John Wayne
Melissa Etheridge



I click on an item in the listbox and the datagridview gets populated with the contents of the tbl_Pets for that particular customer.

So, for example: I click on Bill Gates (in ListBox) and both his pets populate the dgv.

DataGridView
ID
Owners_ID Pets_Name
1
1
Fido
3
1
Bambi



This is the sql I have in hand:
SELECT tbl_Owners.ID AS tbl_Owners_ID, tbl_Owners.Owners_Name, tbl_Pets.ID AS tbl_Pets_ID, tbl_Pets.Owner_ID, tbl_Pets.Pets_Name
FROM tbl_Owners INNER JOIN tbl_Pets ON tbl_Owners.[ID] = tbl_Pets.[Owner_ID];


Thanks,
LCP





Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

LCP2000

Hmmm indeed... I've tried thinking about how to go about putting that WHERE clause in my
statement but with the INNER JOIN in there, I have no clue as to where it would fit in the
syntax. A little too complicated for me at this stage.

As far as the cstr(PersonID), how does this come into play I don't believe I've come across this yet.
Would this be getting the ID off of the listbox


Thanks again,
LCP





Re: Visual Basic Express Edition Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

ReneeC

by the way you want to create an event handler (ListBox.SelectedIndexChanged) and put this code in it.

It looks like your Select statement is ok. In the datagrid view, each column has a Hidden property so a column won't display if you don't want it too. You could also use a list view for this if you wanted.