By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

Filter records in listview using sql statement.

P: 40
I am creating a VB6 project that uses listview as my datagrid. So far I am successful in adding, editing, deleting and refreshing the table (I'm talking about the listview). My problem now is to do searching or filtering the records. This is my code on refreshing my table. I am thinking to use it as basis in doing the "filter records" event. I have added where clause on my sql statement but there is no record appearing in my listview table. Can anyone out there know how make it possible?

Expand|Select|Wrap|Line Numbers
  1. Dim list As ListItem
  2. Dim strSQL As String
  3.  
  4.     strSQL = "SELECT tbl_inventory.item_code,"
  5.     strSQL = strSQL & " tbl_inventory.PK,"
  6.     strSQL = strSQL & " tbl_inventory.item_name,"
  7.     strSQL = strSQL & " tbl_inventory.quantity,"
  8.     strSQL = strSQL & " tbl_inventory.unit_price,"
  9.     strSQL = strSQL & " tbl_inventory.status,"
  10.     strSQL = strSQL & " tbl_category.category_name,"
  11.     strSQL = strSQL & " tbl_supplier.supplier_name"
  12.     strSQL = strSQL & " FROM ((tbl_inventory INNER JOIN tbl_supplier"
  13.     strSQL = strSQL & " ON tbl_inventory.supplierFK=tbl_supplier.PKsup)"
  14.     strSQL = strSQL & " INNER JOIN tbl_category"
  15.     strSQL = strSQL & " ON tbl_inventory.categoryFK=tbl_category.PKcat)"
  16.  
  17.  
  18.  
  19. Set obj = New ADODB.Command
  20. Set obj.ActiveConnection = con
  21.  
  22.     obj.CommandText = strSQL
  23.  
  24. Set rs = obj.Execute
  25.  
  26.     lvProd.ListItems.Clear
  27.  
  28. With rs
  29.  
  30.         Do Until .EOF
  31.             Set list = lvProd.ListItems.Add(, , !item_code & "", , "Prods")
  32.             list.SubItems(item_name_idx) = !item_name & ""
  33.             list.SubItems(qty_idx) = !quantity & ""
  34.             list.SubItems(unit_price_idx) = !unit_price & ""
  35.             list.SubItems(status_idx) = !Status & ""
  36.             list.SubItems(supplier_idx) = !supplier_name & ""
  37.             list.SubItems(category_idx) = !category_name & ""
  38.             list.SubItems(PK_idx) = CStr(!PK)
  39.             .MoveNext
  40.         Loop
  41.  
  42.  
  43. End With
  44.  
  45. With lvProd
  46.     If .ListItems.Count > 0 Then
  47.         Set .SelectedItem = .ListItems(1)
  48.         lvProd_ItemClick .SelectedItem
  49.     End If
  50. End With
  51.  
  52. Set rs = Nothing
  53. Set obj = Nothing
If you have difficulty analyzing my codes or my question please post your reply. I just want this project to be a success. Thanks!
Dec 17 '07 #1
Share this Question
Share on Google+
11 Replies


Expert 5K+
P: 8,434
Not sure I'm understanding the question 100%, but it sounds as though you just got the WHERE clause wrong. Can you show it to us?
Dec 17 '07 #2

P: 40
This code is actually attached in my cmdSearch button. I think this statement is right. My problem now is the code after my SQL statement. It is actually a running code but it displays no records. I also used this whole code to refresh the records in my listview table. It displays all the records in the recordset. No problem in displaying all the records. The problem is filtering the records. What I mean is, I want to search particular records based on the txtsearch textbox. Hope you understand.


Expand|Select|Wrap|Line Numbers
  1. Dim obj         As ADODB.Command
  2. Dim rs          As Recordset  
  3.  
  4. strSQL = "SELECT tbl_inventory.item_code,"
  5. strSQL = strSQL & " tbl_inventory.PK,"
  6. strSQL = strSQL & " tbl_inventory.item_name,"
  7. strSQL = strSQL & " tbl_inventory.quantity,"
  8. strSQL = strSQL & " tbl_inventory.unit_price,"
  9. strSQL = strSQL & " tbl_inventory.status,"
  10. strSQL = strSQL & " tbl_category.category_name,"
  11. strSQL = strSQL & " tbl_supplier.supplier_name"
  12. strSQL = strSQL & " FROM ((tbl_inventory INNER JOIN tbl_supplier"
  13. strSQL = strSQL & " ON tbl_inventory.supplierFK=tbl_supplier.PKsup)"
  14. strSQL = strSQL & " INNER JOIN tbl_category"
  15. strSQL = strSQL & " ON tbl_inventory.categoryFK=tbl_category.PKcat)"
  16. strSQL = strSQL & " WHERE tbl_inventory.item_name='" & txtSearch.Text & "'" 
  17.  
  18.  
  19. Set obj = New ADODB.Command
  20. obj.ActiveConnection = con
  21.  
  22. obj.CommandText = strSQL
  23.  
  24. Set rs = obj.Execute
  25.  
  26. frmInventory.lvProd.ListItems.Clear
  27.  
  28. With rs
  29.  
  30.     Do Until .EOF
  31.         Set list = frmInventory.lvProd.ListItems.Add(, , !item_code & "", , "Prods")
  32.         list.SubItems(item_name_idx) = !item_name & ""
  33.         list.SubItems(qty_idx) = !quantity & ""
  34.         list.SubItems(unit_price_idx) = !unit_price & ""
  35.         list.SubItems(status_idx) = !Status & ""
  36.         list.SubItems(supplier_idx) = !supplier_name & ""
  37.         list.SubItems(category_idx) = !category_name & ""
  38.         list.SubItems(PK_idx) = CStr(!PK)
  39.         .MoveNext
  40.     Loop
  41.  
  42. End With
Dec 17 '07 #3

Expert 5K+
P: 8,434
Sorry, I'm still a little unclear. Are you saying that without the WHERE clause it shows all records, and with the WHERE clause it shows none?
Dec 18 '07 #4

P: 40
Exactly.. but when i put

WHERE tbl_inventory.PK=2

it shows all the records that has an item code which is 2...

The datatype for PK is int..and the datatype for item_name is char.. is it because of the datatype??

Or does the following code after my SQL statement wrong??
Dec 18 '07 #5

Expert 5K+
P: 8,434
Seems to me the WHERE clause is the crux of the matter. I haven't gone through the code in great detail yet, but you should be able to determine as soon as you execute the query whether it's returning any records or not. If it isn't, then you can ignore the rest of the code. If it is, but they're not being handled properly, then we can forget about the WHERE clause and look at the later code.

It's important to narrow down where the problem is.

Can you try out your query string in MS Access (or whatever your database) to make sure it works? It will probably be easier to debug there.
Dec 18 '07 #6

Expert 5K+
P: 8,434
P.S. If your search term includes a wildcard (such as "*") then I don't believe it will match. You would probably need to use the Like operator in that case.
Dec 18 '07 #7

P: 30
I am creating a VB6 project that uses listview as my datagrid. So far I am successful in adding, editing, deleting and refreshing the table (I'm talking about the listview). ...
If you mean searching using a Textbox and if you type a letter from the textbox your listview will give you automatic output. ex. if you type letter "a" from textbox then your query will execute and search all the words that have letter "a" in your database.

Example
Expand|Select|Wrap|Line Numbers
  1. SELECT item_code FROM tbl_inventory WHERE item_code LIKE '%" & txtsearch.text & "%'
Dec 18 '07 #8

P: 30
Hi, if you mean searching for specific words in your database, just use LIKE to filter the word you want. Ex. if you have a item_code 101 to 110, if you type the 101 item code then your listview will automatically generate the result.

CODE OF TEXTBOX:
Expand|Select|Wrap|Line Numbers
  1. SELECT item_code FROM Table1 WHERE item_code LIKE '%" & txtFilter.text & "%'
  2. 'Put your code here to view the record in your listview 
  3.  
Sorry for my English.
Dec 18 '07 #9

P: 40
ok guys! tnx..

i am now analyzing my project.. if there is still a problem then i will post another question here.. you guys really are helpfuL!!

thanks!
Dec 18 '07 #10

Expert 5K+
P: 8,434
Let us know how it turns out, in any case.
Dec 18 '07 #11

P: 12
I have a textbox called txtretrieve that the user enter the name of an item to retrieve its code. I want to make that when the user enter the first letter the program searcher for all names begining with the letter and fills a msflexgrid with the result. As more letters are entered for the item name then less results are displayed in the msflexgrid. my code is as below:
Private Sub txtRetrieve_Change()
MSFlexGrid1.Clear
Dim this As String
this = Val(txtRetrieve.Text)
R1.Open "SELECT BARCODE,DESCRIPTION,QUANTITY FROM ITEM_MASTER_TBL where DESCRIPTION like ' " & txtRetrieve.Text & " %'", Cn, adOpenDynamic, adLockBatchOptimistic
Call LOADHEADER
Me.MSFlexGrid1.Rows = 2
Me.MSFlexGrid1.Row = 1
'Me.MSFlexGrid1.Rows = R1.RecordCount

While Not R1.EOF

Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 0) = Me.MSFlexGrid1.Row
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 1) = R1.Fields(0)
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 2) = R1.Fields(1)
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 3) = R1.Fields(2)

If Me.MSFlexGrid1.Row = Me.MSFlexGrid1.Rows - 1 Then
Else
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
End If
R1.MoveNext
Wend
R1.Close

End Sub
Any help will be much appreciated. Consider a quick response please.
Oct 8 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.