473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filter records in listview using sql statement.

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
11 9332
Killer42
8,435 Expert 8TB
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
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
Killer42
8,435 Expert 8TB
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
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
klaydze
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
klaydze
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
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
Killer42
8,435 Expert 8TB
Let us know how it turns out, in any case.
Dec 18 '07 #11
depash
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

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

Similar topics

7
by: jdph40 | last post by:
I posted this problem previously and received excellent help from Wayne Morgan. However, I still have an unanswered question. My form (frmVacationWeeks) is opened from the OnClick event of a...
4
by: Nhmiller | last post by:
This is directly from Access' Help: "About designing a query When you open a query in Design view, or open a form, report, or datasheet and show the Advanced Filter/Sort window (Advanced...
16
by: Nhmiller | last post by:
I already have a report designed under Reports. When I use filtering in Forms or Tables, I see no way to select that filtered list when I am in Reports, and there appears to be no way to do the...
8
by: swilson513 | last post by:
In Access97 I was able to have an advance filter on a form that had a Like statement so when you applied the filter EACH time it would asked for the criteria. In 2000 the same filter doesn't asked...
3
by: dhowell | last post by:
In reading some of the posts on this group, it appears as though it is not strait forward at all to filter a form, which has subforms, by criteria which are either on subforms or span more than one...
9
by: dee | last post by:
I'd like to filter by the following criteria: left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767 I have no idea how to do this. Appreciate help.
7
by: Katherine | last post by:
I'm trying to filter the records on the mainform (MailingList) of my database using a field contained in a subform (Donations). I was basing my code off Allen Browne's Access Tips page (here:...
3
by: franc sutherland | last post by:
Hello, I have a report which I filter using the me.filter command in the OnOpen event. Me.Filter = "OrderID=" & Forms!variable_form_name! Me.FilterOn = True I want to be able to open that...
2
maxamis4
by: maxamis4 | last post by:
Hello folks, I am still moving into the world of sql server and I need some help. In oracle there is a statement that is called "in list". Basically means what ever is returned from a select...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.