SQL Select query for different combination of arguments 1

[steve1rm]

Hello,

SQL Server 2005, VS 2005

I have a checked list box. The user will select a combination of staff members from the checked list box. This will display these staff members and their tasks that they have to do. Also I have a additional 4 check boxes on the form, where the user can select the prority to be shown. i.e from the checked list box, check staff members 1, 5, 8, 12. and display the prority as high. There 4 prority check boxes (low, normal, urgent, and very urgent)

All are dislayed in a datagrid on the form.

Currently there a 15 staff member that will be in the checked list box, but this could grow as more stafff members are added.

My problem is Writing a select query for each of the possible combinations of staff members and the prority.

I could end up writing many many select queries for each combination.

Is there a simple way to write a select query for this type of situation?

Many thanks in advance,

Steve

 

[jebenson]

First create a string with the part of the SQL that won't change from one query to the next, like so:

Dim SQLStr As String

SQLStr = "Select * from <TableName> where "


Next loop through the checked items of the listboxs and add the checked items to the SQL. I don't know if you are using the actual names or if you have an ID number, so I am using a hypothetical StaffID for this example.

SQLStr &= " StaffID IN ("

For Each drv As DataRowView In clbPONumbers.CheckedItems
drv("StaffID").ToString() & ","
Next

'Remove the last comma
SQLStr = SQLStr.SubString(0, SQLStr.Length - 1)

'add the closing parens
SQLStr &= ")"

'now, look at the checkbox criteria
If chkLowPriority.Checked Then
SQLStr &= " and Priority='Low'"
End If

If chkNormalPriority.Checked Then
SQLStr &= " and Priority='Normal'"
End If

If chkUrgentPriority.Checked Then
SQLStr &= " and Priority='Urgent'"
End If

If chkVeryUrgentPriority.Checked Then
SQLStr &= " and Priority='VeryUrgent'"
End If

So, for the example you gave in your original post, you should end up with this SQL:

Select * from <TableName> where StaffID IN (1,5,8,12) and Priority='Urgent'

Post back if you have any questions.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira