Checkbox Form to create SQL string to query DB

[kgjeremytw]

Hello, I'm needing a bit of help to set up an SQL query that is generated from a check box form.

Background on the DB:

tblTechnologyLookup
OfferingID, TechnologyID, RoleID

tblOfferings
offeringID, offeringTitle

What I have is a form with checkboxes where each checkbox is named by the technologyID or roleID (<INPUT TYPE="checkbox" NAME="[technologyID]" value="1">). I've called this page prescriptionbuilder.asp.

When users click on the various roles and technologies that they want to search and hit the submit button, I need the page that the form posts to, (perscrption.asp), to return the search results. At a minimum the user could click on one role and one technology. The total number of possible roles is 5 and the current number of technologies is 233.

I know that the 233 categories is a bit high, but I can't limit the users. I also need to be able to filter duplicates from the results, as offerings in the tblTechnologyLookup table may have more than one entry.

I'm new to asp and vbscript, I need to know how to set up perscription.asp to query the DB.

Any assistance would be greatly apprecitated. Thanks for all of your help. Jeremy

[Roy Sinclair]

Since you can have multiple checkboxes checked for the SQL you want to build a string of the TechnologyIDs returned separated by commas. Then as you build your SQL statement you can use that string in the where clause to bring in all the technologies like this:

where TechnologyID IN (1,23,27,66)


That's the same as:

where TechnologyID = 1 or TechnologyID = 23 or TechnologyID = 27 or TechnologyID = 66


The SQL IN clause makes such a selection a whole lot easier.

[whammy]

What roy said. I often use this technique not only with checkboxes, but also multiple selects (makes for a really functional search with less coding that way!).

[kgjeremytw]

Thanks for your help, I've trying to figure this out for awhile now. I'm a bit confused on how I would set if statements and variables. Would I have a variable for each checkbox, or is it an if statement to dynamically fill IN (X,X,X,X) statement.

THanks, Jeremy