locked
Trying to get listbox to populate datagridview from an access db w/multi-tables - VB05e

    Question

  • 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
    Tuesday, July 03, 2007 12:36 AM

Answers

  •  

    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


     

     

    Wednesday, July 04, 2007 3:03 AM
  • Ok, got the error fixed. It seems that it was an issue with ListBox1.SelectedItems.Clear().
    I'm using the following to fill the listbox:

    'Fill our ListBox
    ListBox1.BeginUpdate()
    ListBox1.SelectedItems.Clear()
    ListBox1.DataSource = objDataSet.Tables(0)
    ListBox1.DisplayMember = "tbl_Pets"
    ListBox1.ValueMember = "Pets_Name"
    ListBox1.EndUpdate()


    Removing ListBox1.SelectedItems.Clear() fixed the problem.

    Oh well, guess I can live without it for now...

    LCP
    Saturday, July 07, 2007 7:31 PM

All replies

  •  

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

     

    Tuesday, July 03, 2007 5:14 AM
  • 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
    Tuesday, July 03, 2007 11:46 AM
  •  

     

    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.

    Tuesday, July 03, 2007 12:40 PM
  • 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
        

    Tuesday, July 03, 2007 1:07 PM
  • 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
    Tuesday, July 03, 2007 2:21 PM
  •  

    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


     

     

    Wednesday, July 04, 2007 3:03 AM
  • 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
    Wednesday, July 04, 2007 3:52 AM
  •  

     

    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.

    Wednesday, July 04, 2007 4:04 AM
  • 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
    Wednesday, July 04, 2007 4:32 AM
  •  

     

    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.

    Wednesday, July 04, 2007 4:55 AM
  •  

     

    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



     

     

    Wednesday, July 04, 2007 5:18 AM
  • 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
    Wednesday, July 04, 2007 5:34 AM
  • 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
    Wednesday, July 04, 2007 5:47 AM
  •  

    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.

    Wednesday, July 04, 2007 5:55 AM
  • Great thanks, I will try it this eve and get back to you...


    Thanks for your help,
    LCP
    Wednesday, July 04, 2007 6:13 AM
  •  

    Here's an actual SQL statement in one of my projects. It is a stored procedure on the server and the difference that will make is in the @Param parameter name.

     

    Select scg.SCG_ID,scg.SCG_Category, scg.SCG_Description
    From ServiceCategories_General scg
       left outer join OrganizationServiceCategories_General oscg
          on (oscg.OSCG_SCG_ID = scg.SCG_ID and oscg.OSCG_O_ID = @Param1 )
    Where oscg.OSCG_SCG_ID is null order by scg.SCG_Category

    The blue is the relationship joining the tables. the red is a paramater such as might come from a listbox or other GUI. It's the parameter that tells the software what to look for.  And with that I'm going to say good night and I'll see you in the morning.
    Wednesday, July 04, 2007 6:39 AM
  • I'll have to play around with that. Now, how about getting something from the listbox.
    I have this code:
       
    Code Snippet

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
            lblTemp.Text = ListBox1.SelectedIndex.ToString
     End Sub



    When I run it, it returns the selected index = 0. Can I get it to return some other field with in the same row as the selected item?


    Thanks,
    LCP
    Wednesday, July 04, 2007 1:59 PM
  •  

     

    These worked for me:

     

    Private Sub ListBox1_SelectedIndexChanged _

                              (ByVal sender As Object, _

                               ByVal e As System.EventArgs) _

                               Handles ListBox1.SelectedIndexChanged

            Dim a As String = ListBox1.SelectedIndex.ToString

            Dim b As Integer = ListBox1.SelectedIndex

        End Sub

     You can also use the SelectedValue event and the selectedValue property

    Wednesday, July 04, 2007 3:47 PM
  • I had tried this already but unfortunately, this won't work. All it seems to return is the position
    of the item in the listbox. I would like to get a database column field returned.

    For example:
    The listbox is populated with tbl_Pets.Pets_Name. When a pets name in the listbox is selected,
    I would like it to return say, tbl_Pets.Owners_ID.

    Is this possible with a listbox, am I going about it all wrong? Maybe a listview is a better choice.


    Thanks again,
    LCP
    Wednesday, July 04, 2007 4:59 PM
  •  

    First of all SelectedValue should get you the string... but there's another way to that....

     

    Dim a as string = listbox1.items(selectedindex)

     

    will get you the string from the listbox items collection. You're getting close!!!!! Don't give up.

     

     

    Wednesday, July 04, 2007 6:01 PM
  • Well, I've just about given up with the list box. I've been trying to do it with two
    DataGridViews and so far I have had better luck. I'm using PetsDGV to simulate a listbox.
    Ive gotten data into it, now I need to click on a cell and have it populate VisitsDGV. I'm almost
    there...

    I have this funtion setup:

    Code Snippet

    #Region " Filling the DGV's... "
    Private Function FillDGV(ByVal DGVtoUse As Object, ByVal DisplayColumn As String, ByVal MyTable As String, ByVal ComparatorColumn As String, ByVal txtSearch As String)
    DGVtoUse.DataBindings.Clear()

    ' Tablename is a string containing the table name
    Dim OrderString As String = "WHERE " & ComparatorColumn & " = " & txtSearch & ""
    Dim sqlString1 As String = "Select " & DisplayColumn & " from " & MyTable & " " & OrderString

    Dim objDataAdapter As New OleDbDataAdapter(sqlString1, objConnection)
    Dim objDataSet As DataSet
    Dim objDataView As DataView

    ' Initialize a new instance of the DataSet object...
    objDataSet = New DataSet()

    ' Fill the DataSet object with data...
    objDataAdapter.Fill(objDataSet, MyTable)

    ' Set the DataView object to the DataSet object...
    objDataView = New DataView(objDataSet.Tables(MyTable))


    DGVtoUse.DataSource = objDataView

    Return Nothing

    End Function
    #End Region




    Which I then call with cmdSearchByNumber to fill PetsDGV:

    Code Snippet

    Private Sub cmdSearchByNumber_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSearchByNumber.Click
    If (IsNumeric(txtSearch.Text) = True) Then
    lblTemp.Text = ""
    Call FillDGV(PetsDGV, "Pets_Name", "tbl_Pets", "Owner_ID", txtSearch.Text)
    ElseIf txtSearch.Text = "" Then
    lblERROR.Text = "Search field cannot be empty..."
    lblERROR.Visible = True
    Else
    lblERROR.Text = "You must enter a record number only..."
    lblERROR.Visible = True
    End If
    End Sub




    When I click on a pet name in PetsDGV, I want it to populate VisitsDGV (So far I've com
    3/4's of the way). The real problem I suspect lies in the code below ...

    Code Snippet

    Private Sub PetsDGV_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles PetsDGV.SelectionChanged
    Call FillDGV(VisitsDGV, "Pet_ID AS [Pet ID], Vet_Name AS Veterinarian, Visit_Date AS [Date of Visit]", "tbl_Visits", "Pet_ID", txtSearch.Text)
    lblTemp.Text = PetsDGV.CurrentCell.OwningColumn.Name
    End Sub




    Any insight, I know my code is confusing as it is, but I've been trying several different
    methods of doing this.

    Any further help is greatly appreciated,
    LCP
    Wednesday, July 04, 2007 8:44 PM
  • I'm a little surpised this compiles

     

    Private Function FillDGV(ByVal DGVtoUse As Object, ByVal DisplayColumn As String, ByVal MyTable As String, ByVal ComparatorColumn As String, ByVal txtSearch As String)

     

    Functions are declared with the datatype they return, however you returned Nothing... it would be better to declare that as a subroutine or use and As to declare it's datatype.

     

    Try this........

     

    Private Function FillDGV(ByVal DGVtoUse As Object, ByVal DisplayColumn As String, ByVal MyTable As String, ByVal ComparatorColumn As String, ByVal txtSearch As String) as Datatable

    .

    .

    .

    .

     

    objDataAdapter.Fill(objDataSet, MyTable)

    return mytable
    End Function

     

    then call the function........

     

    Datagridview.datasource = FillDGV( arguments........)

     

    See what happens .... if your select statement is working... display the table.

     

    Wednesday, July 04, 2007 9:50 PM
  • Whooooops! Changed to sub, but it was working as a function as well, as long as
    it had Return Nothing on it...

    Now After the change I get an error:
    Object reference not set to an instance of an object.

    at VisitsDGV.DataSource = objDataView


    Thanks for any insight,
    LCP

    Thursday, July 05, 2007 3:09 AM
  •  

    A function always returning nothing is being misused. I think that's representative of many things in this thread.

     

    I appreciate your politeness, I see several problems, in both the code and your overall approach. I always stress fundamentals because without them, what you see here will occur continually. You're trying something difficult and it doesn't appear you have the fundamentals down yet.

     

    You don't want to mess with views right now. You need to focus on getting the data into the datagridview. I don't know who it was, but they marked one of my early answers correct. It should have been marked correct not because I care about that but it was a pointer to your not paying attention.

     

    1.) Get your data into the table

     

    2.) Assign the table as a datasource to the datagridview.

     

    3.) For the time being forget views until you have 2 down pat.

     

    That's all you need to do and I've actually been saying that for a while now.

    Friday, July 06, 2007 12:37 AM
  • Thanks for your reply. I do appreciate your time on this and ,yes, I have kinda jumped the gun on my learning curve...

     

    Fact is, I do know how to asign a datasource to a datagridview and fill it with data. I know how to bind controls in

    code and design. What I seem to be having trouble in, is having controls display their data based on another controls

    data selection.  My code seems to be jumbled here and there also because I have literaly been copying and pasting

    alot of examples and trying to come up with something that will work for me. I expect to clean it up and fix some of the

    issues as I go, but also plan on a final re-write 'cause I know there are several things that I have overlooked.

     

    I agree that I do need further help on the fundamentals in general. But heck, I'm still working on the fundamentals of life .

    Most of the programs I've ever done are non database or one table database designs, so that's been another learning

    point for me. I've been coding, on and off, since VB4, but always small utilities to help me in my daily work.

     

    This time around I'm hoping to take it a step forward and hopefully get the fundamentals down...

     

     

    Thanks again,

    LCP

    Friday, July 06, 2007 1:44 AM
  •  

    Aren't we all working on the fundamentals of life? We won't get them down in a lifetime.

     

    I've always thought there were vaguenesses in your problem formulation. It's only an impression and I could be wrong, but I don't think you have a clear formulation and until you do, you won't be able to operationalize it.

     

    I'm sure you can state your problem, only I haven't seen it. For example:

     

    " What I seem to be having trouble in, is having controls display their data based on another controls

    data selection."

     

    This doesn't tell me much. It's not clear what you really want to do. Again, much of your enigma is in fundamentals.

     

    You select a user in a listbox and what do you want to occur in the datagridview as result? 

     

    Start in the listbox "Selecteditemschanged" event handler, this should execute the code with your slectstatement, ending with Datagridview.Datasource = Table.

     

    This should reliably fill the DGV.

    Friday, July 06, 2007 4:02 AM
  • Wow, I thought I had expressed myself correctly on some of the other posts. I apologize if I
    seem not to make sense. Yes, it's true, I am struggling with the fundamentals (I guess it's one of
    the draw-backs from trying to teach myself - I jump the gun often). Oh well, back to hitting the
    books ...

    Thanks again,
    LCP
    Friday, July 06, 2007 1:29 PM
  •  

     

    Books aren't going to be as valueable as getting this to work.

     

    I've clearly asked some questions that you really have not answered.......

     

    1.) Is data in your table? If so, is it what you expect?

     

    You can answer this simply in the debugger by looking at the rows and column counts after the fill statement.

     

    if data is going in the table then

     

    Datagridview.datasource = table

     

    Should display it.

     

    Can you answer question 1?

     

     

    you can even do things like this with the table

     

    Dim a as string

    Dim b as string

    For each row as datarow in Table.rows

     

    a = row(0)

    b= row(1)

     

    Next   ' set debugger breakpoint here

     

    this will display the contents of column 0 and 1 for each row. The debugger will show the full row item array simply by clicking on the array. So the questions I have been asking are meaningful and answerable.

     

    Can you answer them?

     

    For some reason the debugger scares new programmer.... but debuggers are your best friend. Take some time and learn to use it. This too is a "fundamental".

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Friday, July 06, 2007 2:28 PM
  • I use this in vb6 to grab a string from a listbox when selected:

     

    Private Sub ListBox2_Click() 'set value
        Dim I As Integer
        For I = 0 To ListBox2.ListCount - 1
            If ListBox2.Selected(I) = True Then
                Length = ListBox2.List(I)
            End If
        Next I
    End Sub

     

    where "Length" is a variable defined "as string". I'm still learning the nuances of .net myself and haven't used listboxes in it yet. Hopefully they haven't changed that much and you may be able to use it with very little modification. After the loop you should be able to use the value you retrieved from the listbox in your SQL and then populate your DGV. Hope this helps some,

    -Josh

    Friday, July 06, 2007 2:31 PM
  •  

     

    Joshua there are better ways to do that in Dot.net. The listbox has a selectedindexchanged event. That way you don't have to scan for the selected event.

    Friday, July 06, 2007 2:33 PM
  • Thanks Renee,

    I'll give that a shot. I may have to come up with a project that uses a listbox just to try it out. Smile

    Friday, July 06, 2007 3:31 PM
  • ok, I reread through some of the posts and created a listbox control to play with. The click event will fire effectively the same as the selectindexchanged event. The difference being that the SIC event will not fire if the user clicks on an item already selected in a listbox set for single selection whereas the click event will fire everytime the user clicks (makes a selection) in the listbox. The SIC event is helpful in this instance, whereas in the case of something like a radio button, the click event is preferrable since it will not fire when the checked value goes false and checkchanged will fire regardless (meaning 2 subs will run everytime a radiobutton is clicked and you have to use validation in each and every sub).

    I think what you meant was the selecteditem property (used in your previous post) means you don't have to iterate the selection. So you can condense 5 lines of code:

     

    For I = 0 To ListBox2.ListCount - 1
            If ListBox2.Selected(I) = True Then
                Length = ListBox2.List(I)
            End If
        Next I

     

    to 2 lines of code (where list is replaced by index):

     

    indexVar = listbox1.selecteditem

    mystringVar = listbox1.index(mystringvar).tostring

     

     or perhaps even 1 line of code:

     

    myStringVar = listbox1.index(listbox1.selecteditem).tostring

     

    Behind the scenes, the getproperty method iterates the index for you so you don't have to do it. Right or Wrong?

    Sorry, I'm not looking to hijack this thread, just found this interesting since I'm currently working with a DGV and radiobuttons,,, I'll steer clear after this and let you help get his code up and going. Smile

    -Josh

    Friday, July 06, 2007 4:31 PM
  •  

    Hi Joshua,

     

    Yes that's one of the many online ways to do it that I was thinking of.

     

    I not a very good woman to ask about the listbox control. That's one of the few vs2005 controls that I don't like and never use. I use the combobox instead.

     

    In vs2005 and VBE as well, a really good resource is the Object Browser. At least, it will provide a short description of all the properties and methods of a given class.

    Friday, July 06, 2007 9:47 PM
  • Hi guys, thanks for all your help, I was able to finish it by using two dgv's instead of the
    listbox-dgv combination.

    Thanks again, I'll be in touch with more technical issues I'm sure...
    LCP
    Saturday, July 07, 2007 4:18 AM
  •  

    Not everyone here is a guy.

     

    Good luck.

    Saturday, July 07, 2007 6:12 AM
  •  ReneeC wrote:

    ...

     

    I not a very good woman to ask about the listbox control. That's one of the few vs2005 controls that I don't like and never use. I use the combobox instead.

     

    I know, My appologies. I have four sisters a wife and 3 daughters and they, as well as I, refer to each other as guys. Force of habit that's not easy to change .

     

    LCP

    Saturday, July 07, 2007 3:25 PM
  •  

    it just dawned on me what your problem is with the listbox.

     

    if I do this.....in code:

     

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles _
                                                                                                                          
    ListBox1.SelectedIndexChanged

                   Dim a As String = ListBox1.SelectedItem

    End Sub

     

    If my items are "Bob","Carol","Ted" and "Alice"    if I click one the items that says "Carol"   "a" will equal Carol" in the event

     

    Consider the Following Select Statement:

     

    Select * in Names where [Name] = 'Bob'

     

    WHat you are interested in is changing your select statement to something along this line:

     

    Select * in NamesTable where [Name] = '" + a + "';"

     

    I know you don't have a where, but you have an equivalent... such a modification will do what you want.

     

    Good luck... you've been really tenacious.

     

    Saturday, July 07, 2007 6:20 PM
  • I've got it, I've got it, woohooo!!! I just had to keep trying with the listbox .

    This is what I've done so far. It still needs work and I'm sure their is a better way to go about it - but it works.
    I'm getting an error I believe to be at Dim VisitsDataAdapter... but have not checked completely yet.
    I added On Error Resume Next above it and now it all works, hmmm.

    Code Snippet
        Private Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
            VisitsDGV.DataBindings.Clear()

            Dim sqlVisits As String = "SELECT Visit_date AS [Date of Visit], Vet_Name AS Veterinarian, Visit_Description AS Description, Visit_Diagnosis AS Dx, Visit_TestsPerformed AS Tests, Visit_Actions AS Actions FROM tbl_Visits WHERE Pet_ID = "

            On Error Resume Next ' Gettin' ERROR Here, Need to research this more.
            Dim VisitsDataAdapter As New OleDbDataAdapter(sqlVisits & ListBox1.SelectedItem(0), objConnection)
            Dim VisitsDataSet As DataSet
            Dim VisitsDataView As DataView

            ' Initialize a new instance of the DataSet object...
            VisitsDataSet = New DataSet()

            ' Fill the DataSet object with data...
            VisitsDataAdapter.Fill(VisitsDataSet, "tbl_Visits")

            ' Set the DataView object to the DataSet object...
            VisitsDataView = New DataView(VisitsDataSet.Tables("tbl_Visits"))

            ' Fill our DataGridView
            VisitsDGV.DataSource = VisitsDataView
        End Sub

    LCP
    Saturday, July 07, 2007 6:43 PM
  • Ok, got the error fixed. It seems that it was an issue with ListBox1.SelectedItems.Clear().
    I'm using the following to fill the listbox:

    'Fill our ListBox
    ListBox1.BeginUpdate()
    ListBox1.SelectedItems.Clear()
    ListBox1.DataSource = objDataSet.Tables(0)
    ListBox1.DisplayMember = "tbl_Pets"
    ListBox1.ValueMember = "Pets_Name"
    ListBox1.EndUpdate()


    Removing ListBox1.SelectedItems.Clear() fixed the problem.

    Oh well, guess I can live without it for now...

    LCP
    Saturday, July 07, 2007 7:31 PM
  •  

    I'm glad you are making progress.

     

    I'll share with you what I do. I never use datasets. I generally don't work with multiple tables on the output side, so you don't see things like objDataSet.Tables(0) , in my code. I don't feed a single column device like a list box an entire table and narrow down for the control

     

    I would

     

    Listbox1.items.clear

    for each row as datarow in table.rows

          listbox1.items.add(row("petname"))

    next

     

    Then I know I have a cleanly loaded control.  It didn't do you any good at all to ListBox1.SelectedItems.Clear(). That going to clear the selected item collection. Fundamentals..............

     

     

    Saturday, July 07, 2007 10:41 PM
  • Fundamentals..............

     

    Some how I new your were going to say that (LOL). Funny, I was just coding a loop for something

    else and didn't even think of doing it that way for the listbox.

     

     

    Thanks again, I'll get the fundamentals. Eventually  .

    LCP

    Saturday, July 07, 2007 10:55 PM
  •  

    We've had long debates here about all these new bindings and views etc and learners using them. They are all black boxes and people lose sight of the actual underlying computing beneath them. I work close to my tables and control what they look like and what data I have on hand.

     

     

    Fundamentals is the new "F-word". Wink Take care.

    Sunday, July 08, 2007 12:44 AM