vb6.0 + sql

Question

ankush.mukherje 0 Junior Poster

14 Years Ago

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

visual-basic

Edited 14 Years Ago by ankush.mukherje because: n/a

3 Contributors
19 Replies
509 Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by ankush.mukherje

QVeen72 104 Posting Shark

14 Years Ago

Hi,

What is your Table Structure..?

Regards
Veena

QVeen72 104 Posting Shark

14 Years Ago

hi,

Post your code what you have done till now..

Regards
Veena

QVeen72 104 Posting Shark

14 Years Ago

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

QVeen72 104 Posting Shark

14 Years Ago

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

sidnei 12 Junior Poster in Training

14 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ankush.mukherje 0 Junior Poster · Answer 1 · 2010-01-19T17:01:38+00:00

Hi
sry but could not get you
It is a general ms-access table

ankush.mukherje 0 Junior Poster · Answer 2 · 2010-01-19T17:12:15+00:00

Please!! Please!! help me ASAP
It's really urgent for me

ankush.mukherje 0 Junior Poster · Answer 3 · 2010-01-19T17:49:11+00:00

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

ankush.mukherje 0 Junior Poster · Answer 4 · 2010-01-19T18:30:34+00:00

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.

ankush.mukherje 0 Junior Poster · Answer 5 · 2010-01-20T10:33:53+00:00

Hi QVeen
Suppose no two categories have same name. Then is it possible?

ankush.mukherje 0 Junior Poster · Answer 6 · 2010-01-20T11:15:07+00:00

ankush.mukherje 0 Junior Poster

14 Years Ago

Hi
Please someone help me out!!
Thanks

QVeen72 104 Posting Shark · Answer 7 · 2010-01-20T11:29:11+00:00

Hi,

did you try Sidnei's suggestion...?
It might help you..

Regards
Veena

ankush.mukherje 0 Junior Poster · Answer 8 · 2010-01-20T11:32:45+00:00

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

ankush.mukherje 0 Junior Poster · Answer 9 · 2010-01-20T11:39:39+00:00

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

ankush.mukherje 0 Junior Poster · Answer 10 · 2010-01-20T11:41:26+00:00

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

QVeen72 104 Posting Shark · Answer 11 · 2010-01-20T12:27:42+00:00

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

ankush.mukherje 0 Junior Poster · Answer 12 · 2010-01-20T12:33:09+00:00

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

ankush.mukherje 0 Junior Poster · Answer 13 · 2010-01-21T16:18:34+00:00

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

ankush.mukherje 0 Junior Poster · Answer 14 · 2010-01-27T11:39:48+00:00

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