How to retrieve data from access table according to listbox contains
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: How to retrieve data from access table according to listbox contains

  1. #1
    Join Date
    Aug 2011
    Posts
    8

    Exclamation How to retrieve data from access table according to listbox contains

    How to retrieve data from access table according to listbox contains

    I have a Data Base Project in Visual Basic 6.0
    I have a access table named Table1 and a ListBox named List1 and a ComboBox named Combo1.
    There are multiple List in the ListBox and ComboBox both.
    Now I pass two list item from another code [Code] to ListBox now there are "Mr" and "Dr" in ListBox and I select "Title" in the ComboBox
    Now How how can I retrieve data from Table 1 (Title Field)
    The following code did not work.
    Sub List()
    'Database connection already Defined
    For LI = 0 To List1.ListCount - 1
    RecordSet.Open ("select * from Table1 where " & Combo1.Text & _
    " like '" & List1.List(LI) & "%'"), Connection, adOpenDynamic, adLockOptimistic
    Next LI
    End Sub

    Further Clarification

    ListBox Contains
    Mr
    Dr

    Data in Table1
    Mr. SKB
    Dr. GRB
    Mr. LNK
    Ms. RPK

    Result (anywhere like flexgrid, textbox etc)
    Dr. GRB
    Mr. SKB
    Mr. LNK

  2. #2
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: How to retrieve data from access table according to listbox contains

    #1 the column that this data is in is it called 'Title' ????

    #2 In your code snip (BTW please use CODE tags) i dont see you doing anything with the data from the Recordset.. (putting it into a flexgrid etc...)
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,945

    Re: How to retrieve data from access table according to listbox contains

    Why would you want to open the table connection once for each record in the table? If everyone did that, it'd kill performance. Find ALL records, then FILTER the data in the query

    (Unless you do have a million records, in which case you'd load 50 filtered records at a time(
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: How to retrieve data from access table according to listbox contains

    Definitely do not open a new recordset for every record in a loop. Also you must close a recordset before you can open it again.

    You can filter or you can just load all the records into a recordset and loop through them for matches. With your small data set this would work fine but would not be a good idea with a large data set.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,722

    Re: How to retrieve data from access table according to listbox contains

    If we assume your combobox contain the field names and the listbox the possible choices, you would make up the query string like this:
    Further assume a Recordset named Rec and an open connection Con.
    Code:
      dim sql as string
      sql = "SELECT * FROM Table1 WHERE (" & Combo1.Text & " LIKE '" & List1.List(List1.Listindex) & "')"
      Rec.Open sql, Con, adOpenDynamic, adLockOptimistic
      Set FlexGrid.DataSource = Rec
    You first build a proper SQL string to query the wanted data, then you open the recordset with this string which will already load all the records which match the query. No loop is needed.
    The last lign assigns the recordset as DatSource for an assumed FlexGrid to display the found records.

    Please note how you use the selected item from the listbox.

  6. #6
    Join Date
    Aug 2011
    Posts
    8

    Unhappy Re: How to retrieve data from access table according to listbox contains

    dim sql as string
    sql = "SELECT * FROM Table1 WHERE (" & Combo1.Text & " LIKE '" & List1.List(List1.Listindex) & "')"
    Rec.Open sql, Con, adOpenDynamic, adLockOptimistic
    Set FlexGrid.DataSource = Rec

    This code returns only one search criteria (one at once Either "Mr" or "Dr")
    When I select "Mr" in Listbox it returned
    Mr LNK
    Mr SKB
    And when I select "Dr" in Listbox it returned
    Dr GRB
    But when I select both lists it returned only one item, i select at last.
    How to retrieve the data according to selected lists ("Mr" and "Dr")?
    Which can show
    Dr GRB
    Mr LnK
    Mr SKB

  7. #7
    Join Date
    Aug 2011
    Posts
    8

    Re: How to retrieve data from access table according to listbox contains

    "List" does not means Listbox but contains "row" of it. I have only one list box "List1"

  8. #8
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,945

    Re: How to retrieve data from access table according to listbox contains

    Need some LOGIC to test which is which. Then, one statement can be

    Code:
    & " LIKE ('" & List1.List(List1.Listindex) & OR List1.List(List1.Listindex +1)"')"
    Also, a string can contain Dr, MD, or even Dr OR MD
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center