-
August 2nd, 2011, 07:11 AM
#1
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
-
August 2nd, 2011, 08:18 AM
#2
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.
-
August 2nd, 2011, 10:16 AM
#3
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(
-
August 2nd, 2011, 10:37 AM
#4
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.
-
August 2nd, 2011, 10:40 AM
#5
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.
-
August 4th, 2011, 10:25 AM
#6
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
-
August 4th, 2011, 10:27 AM
#7
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"
-
August 4th, 2011, 10:49 AM
#8
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|