VB6 and SQL help needed

[Pendaz]

This is kinda hard for me to explain so please bare with me here...


I have 2 list boxes and 3 text boxes which display info from a database.

Item Catagory | Item No. | Description | Qty | Type


The user should be able to make a selection from Item catagory which will then filter and only display the items that match that catagory. The user will then select from the Item No. listbox to display the required record in the rest of the fields...



I have the data reading into the list box using some example code i got from this page. which works fine:

'turn MousePointer to HourGlass to show that we are busy processing
    Me.MousePointer = vbHourglass
    
    'instantiate the connection object
    Set cn = New ADODB.Connection
    'specify the connectionstring
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\data\data.mdb"
    'open the connection
    cn.Open
    
    'instantiate the recordset object
    Set rs = New ADODB.Recordset
    'open the recordset
    With rs
        .Open "test_data", cn, adOpenKeyset, adLockPessimistic, adCmdTable
           
        'loop through the records until reaching the end or last record
        Do While Not .EOF
            CMBItemCat2.AddItem rs.Fields("Filter")
            rs.MoveNext 'moves next record
        Loop
        
        If Not (.EOF And .BOF) Then
            rs.MoveFirst    'go to the first record if there are existing records
            'FillFields      'to reflect the current record in the controls
        End If
        
    End With
    
    
  'Below is code to add data from database to CMBItemCat1
    
  Set rs = New ADODB.Recordset

                           'Load the data
'** change this SQL to load the data you want.
  strSQL = "SELECT filter from test_data"

'** change oConn to the name of your Connection object
  rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
                           'Fill the combo box (or ListBox)
'** change the name of the combo to the one you want to fill
  With CMBItemCat1
    .Clear
    Do While Not rs.EOF
'** change the name of the field here to the one you want to show
      .AddItem rs.Fields("Filter").Value
     ' .ItemData(.NewIndex) = rs.Fields("Filter").Value
      rs.MoveNext
    Loop
  End With

                           'Tidy up
  rs.Close
  Set rs = Nothing
  
  Me.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
End Sub

but i struggle to filter the data in the next few fields.


Heres what i have, but i figure its something wrong with the SQL statement:

Private Sub CMBItemCat1_LostFocus()
Dim item As String
ItemName = CMBItemCat1.Text
  Set rs = New ADODB.Recordset

                           'Load the data
'** change this SQL to load the data you want.
  strSQL = "SELECT TABLE.Part FROM Test_data WHERE Filter =[%0]"
  
   
  
  

'** change Conn to the name of your Connection object
 rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
                           'Fill the combo box (or ListBox)
'** change the name of the combo to the one you want to fill
  With CMBItemNo
    .Clear
    Do While Not rs.EOF
'** change the name of the field here to the one you want to show
      .AddItem rs.Fields("part").Value
     ' .ItemData(.NewIndex) = rs.Fields("Filter").Value
      rs.MoveNext
    Loop
  End With

                           'Tidy up
  rs.Close
  Set rs = Nothing
End Sub

Really hope someone can help me with this as my deadline for it is Wednesday :/


Thanks in advance

-Pendaz


**EDIT**

I figured it might help if i shown some example data: heres a screenshot of the data table:

http://i45.tinypic.com/av4w87.jpg



So the users must select an item from "Filter_1" which will then only show items that = the selection, THEN the user will select from the "Part" field to show only one entry.

[GaryMazzone]

What is Table.Part in you query? Where is the Table table (bad nameing here) in the From clasue? What field is Filter in the table you are selecting from? The where condition for a Like search is:

Where FieldName LIKE = '%SomethingYouAreSearchingFor' (If a string) and you want the field to end with the search crieteria.

[Pendaz]

Hey thanks for the reply


"Part" is a column in the database (see here: http://i45.tinypic.com/av4w87.jpg)

The "Table.Part" in my code was given to me by someone else to test, it diddn't work and i've now removed it.

The SQL now stands like this:

strSQL = "SELECT Part FROM Test_data WHERE Filter LIKE = '%ItemName'"

(Item name is a variable i have set in VB which = the text in the ""Item Catagory" List box

On run i get the following error:

Syntax error (Missing Operator) in query expression 'Filter LIKE = '%ItemName'

[GaryMazzone]

OK then what is the column name you want to filter on (Filter?) that is the name that should go before the Like and Before the Where.

If ItemName is a var in your program it needs to be outside the sql string and not included but concatinated into the string.

[Pendaz]

The column name is "part" (part number) i want to display only the part numbers where filter_1 matches the users selection.

ItemName is a variable in my VB Code... how do i use it in the sql string?


would it be easier if i just sent the project to you?

[GaryMazzone]

I will not work on you package, but I will help you here.

Like this (find only where the entered data is at the end of the part field):

strSQL = "SELECT Part FROM Test_data WHERE part LIKE = '%" & ItemName & "'"

Or Like this (find only where the entered data is at the start of the part field):

strSQL = "SELECT Part FROM Test_data WHERE part LIKE = '" & ItemName &"%"

Or Like this (find any where the entered data in the part field):

strSQL = "SELECT Part FROM Test_data WHERE part LIKE = '%" & ItemName &"%"

[Pendaz]

getting syntax error (missing operator)

[GaryMazzone]

IS the table named test_data or TestData? Do you want to filter on Filter_1 or Part? replace the appropriate values.

From you image it should be:

strSQL = "SELECT Part FROM Testdata WHERE Fitlter_1 LIKE = '%" & ItemName &"%'"

also What is what is the actual variable name

[Pendaz]

The table is named: test_data
I want to filter on: filter_1

so i have this:

strSQL = "SELECT Part FROM Test_data WHERE filter_1 LIKE = '%" & ItemName & "%"

which still returns a syntax error

[GaryMazzone]

You are missing a closing single qoute after the last wild card (&#37

strSQL = "SELECT Part FROM Test_data WHERE filter_1 LIKE = '%" & ItemName & "%' "

[Pendaz]

still same error on run :S

[GaryMazzone]

Please post the exact sql that is being run. DO you know how to set break points? Watches?

[Pendaz]

strSQL = "SELECT Part FROM Test_data WHERE filter_1 LIKE = '%" & ItemName & "%' "

and no i dont know how to set break points / watches?

[GaryMazzone]

I want to see the actual sql generated not the code to build.

To be simple just add a textbox on the form and set the text to the sql statement

[Pendaz]

sorry, i'm still (obviously) learning all this so please bare with me


text2.text = strSQL

gives the following:

SELECT filter from test_data

[GaryMazzone]

so we cas see that the result you posted does not look like the code we were writing.

Now you need to set a break point in the code and step though the code line by line to see what is happing at each point.

You do that in the code module and click in the area on the left side of the code module (a grey line on the side of the white code wrting area) on a line at the start of the section you want to walk though. The application will stop running and show you the code window with the line highlighted. Now you use the F8 key to step to the next line of code. Check that the variables are setting to. The strSQL should look like the code we were writting above.

PS this is a very I repeat EXTREAMLY IMPORTANT concept to understand and get the ability to debug your code.