I have a list box in my vb form.
The list box is dynamically filled with components from my ms-access table.
Now the user can select one, two or many options.
For example:
The list box has many softwares.
1. If user selects Windows XP and Office 2007 then those data will be shown which has XP in windows column and Office 2007 in Office column. Else it will show no data found.

2. If user selects Windows XP and Office 2007 and Acrobat 8 then those data will be shown which has XP in windows column and Office 2007 in Office column and Acrobat in PDF_Writer column. Else it will show no data found.
I have tried a lot but could not get the desired result.
please help
thank you in advance
please help ASAP

Edited 6 Years Ago by ankush.mukherje: n/a

Set adorsRAM = New ADODB.Recordset
 
  Criteria = ""
    For Cnt = 0 To lstsoftware12.ListCount - 1
        If lstsoftware12.Selected(Cnt) Then
            If Len(Criteria) > 0 Then Criteria = Criteria & ","
            Criteria = Criteria & "'" & lstsoftware12.List(Cnt) & "'"
            End If
    Next

    If Len(Criteria) = 0 Then
        MsgBox "You must select at least one item in each categorised software.", vbInformation, "Error"
        msh.Refresh
        msh.Clear
        msh.Rows = 2
        msh.Cols = 2
        Exit Sub
    End If
    
    sSQL = "SELECT * FROM Master_final WHERE Windows IN (" & Criteria & ")"
     sSQL = sSQL + "and Office IN (" & Criteria & ")"
     sSQL = sSQL + "and AUTOCAD IN (" & Criteria & ")"
     sSQL = sSQL + "and PDF_Writer IN (" & Criteria & ")"
     sSQL = sSQL + "and Primavera IN (" & Criteria & ")"
     sSQL = sSQL + "and Etap IN (" & Criteria & ")"
     sSQL = sSQL + "and Tally IN (" & Criteria & ")"
     sSQL = sSQL + "and Caesar IN (" & Criteria & ")"
     sSQL = sSQL + "and Staad IN (" & Criteria & ")"
      End If

Set adorsRAM = cn.Execute(sSQL)
    If Not adorsRAM.EOF Then
    Set msh.DataSource = adorsRAM
    msh.FormatString = "DATA No."
    For i = 1 To (msh.Rows - 1)
        msh.TextMatrix(i, 0) = i
    Next i
    msh.Refresh
    Else
    MsgBox "No Records Found in DataGrid!"
    msh.Clear
    msh.FixedCols = 1
    msh.FixedRows = 1
    msh.FormatString = "DATA No."
    End If

this is the code
this is working perfectly fine when I am selecting a software of each type.
But suppose a user selects only a type of Window and a type of Office then the output is not show whereas I want the out put to be printed in that situation

HI,

WITH WHAT FIELD IS THE "lstsoftware12" POPULATED...?
YOU CANT GIVE SAME CREITERIA for all the where conditions in SQL Query......
Why not post the table structure as well (even if it is simple...)....

Usually in such conditions.. you will need to have as many selection ListBoxes as much as in Where condition...
WindowsListBox, OfficeListBox, AutoCADListBox etc.....
and each of them populated with their respective fields seperately...

In your design, you are having only one listbox ... which will not work


Regards
Veena

Hi
The lstsoftware12 is populated with distinct datas of 9 columns
I also thought it may not be possible but this is the requirement of the user so I am helpless.

Edited 6 Years Ago by ankush.mukherje: n/a

Hi,

Unless you pouplate 9 different Listboxes, and build sql string accordingly, it may not be possible...
Say if same name is there in 2 different catagories, how will you identify which one is category selected..?
You have to make the user Understand....

Regards
Veena

Hello, friend!

I think you don't understand the exact SQL code you write...
See this: if IT'S RAINING AND CLOUDY then you get a rainy weather... but is really difficulty to RAIN without CLOUDS... get the idea?

The SELECT instruction you wrote point to OFFICE IN <specified criteria> AND WINDOWS IN <another specified criteria>, so, the user NEED to select a criteria for Windows, AND for Office.


I suggest that you change your instruction and make some tests.
If the user can specify one OR another criteria, you CAN'T use AND operator.
But the OR operator, take in mind, gets the record that satisfies one OR another condition, and I think you can try it but it will not function like you desire, seems to me that this operator will get entire table - all records.


Try this:

sSQL = "SELECT *" & _
       "  FROM MASTER_FINAL" & _
       " WHERE WINDOWS IN (" & CRITERIA & ")" & _
       " UNION " & _
       "SELECT *" & _
       "  FROM MASTER_FINAL" & _
       " WHERE OFFICE IN (" & CRITERIA & ")" & _
       " UNION " 'add ALL you criteria like a new SQL query

Note the UNION command preceed each new SELECT string you make; it means that the last SELECT instruction don't use other UNION keyword.
I'm not so good with words, take this example:

SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4

Try this and post the results you get.


Good luck!
Sidnei

Set adorsRAM = New ADODB.Recordset
 
  Criteria = ""
    For Cnt = 0 To lstsoftware12.ListCount - 1
        If lstsoftware12.Selected(Cnt) Then
            If Len(Criteria) > 0 Then Criteria = Criteria & ","
            Criteria = Criteria & "'" & lstsoftware12.List(Cnt) & "'"
            End If
    Next

    If Len(Criteria) = 0 Then
        MsgBox "You must select at least one item in each categorised software.", vbInformation, "Error"
        msh.Refresh
        msh.Clear
        msh.Rows = 2
        msh.Cols = 2
        Exit Sub
    End If
    
    sSQL = "SELECT * FROM Master_final WHERE Windows IN (" & Criteria & ")"
     sSQL = sSQL + "and Office IN (" & Criteria & ")"
     sSQL = sSQL + "and AUTOCAD IN (" & Criteria & ")"
     sSQL = sSQL + "and PDF_Writer IN (" & Criteria & ")"
     sSQL = sSQL + "and Primavera IN (" & Criteria & ")"
     sSQL = sSQL + "and Etap IN (" & Criteria & ")"
     sSQL = sSQL + "and Tally IN (" & Criteria & ")"
     sSQL = sSQL + "and Caesar IN (" & Criteria & ")"
     sSQL = sSQL + "and Staad IN (" & Criteria & ")"
      End If

Set adorsRAM = cn.Execute(sSQL)
    If Not adorsRAM.EOF Then
    Set msh.DataSource = adorsRAM
    msh.FormatString = "DATA No."
    For i = 1 To (msh.Rows - 1)
        msh.TextMatrix(i, 0) = i
    Next i
    msh.Refresh
    Else
    MsgBox "No Records Found in DataGrid!"
    msh.Clear
    msh.FixedCols = 1
    msh.FixedRows = 1
    msh.FormatString = "DATA No."
    End If

this is the code
this is working perfectly fine when I am selecting a software of each type.
But suppose a user selects only a type of Window and a type of Office then the output is not show whereas I want the out put to be printed in that situation

Hi
I did tried that solution but the outcome is all the records in each category not the common records.
Thanks

Hi
Is there no better option then making 9 different list boxes for each column and then making the user select options?
Thank You

Hi
The user has done this kind of job in VB.Net and now requires the same kind of result from me in VB6.0.
Therefore I wanna know is it possible in vb6.0.
Thank you

Hi,

If it is done in VB.net, you can check the code, how it is done...
you can implement the same logic here in VB6..
VB syntax will chenge but SQL Query would remain the same..

Regards
Veena

Hi QVeen
Thanks for your valuable suggestion
I will try as you have said and report back again
Thank you again

Hi
Probably posting after a long break.
I had tried all means to do the work but is getting failed each time.
please suggest some ways to me.
Thank you in advance

Hi to all
with help from all of you I have been succesfully able to complete my project
I want to specifically thank SIDNEI who has helped me a lot.
Thanks Sidnei.
and yes to QVeen also
you guys are gr8
thumbs up to u all

This question has already been answered. Start a new discussion instead.