Using values from a combo box as variables in SQL

SoC · July 18th, 2004, 07:31 PM

Hi,

I have a form which consists of several checkboxes and a text area, which enables users to search a database.

I would like to incorporate one or more combo boxes to help filter their search.

Can someone give me some idea how to do this in VBScript?

Here is the code I'm using:

Code:

<%
Dim tisSearch__varTis
tisSearch__varTis = "null"
If (Request.Form("tisField") <> "") Then 
  tisSearch__varTis = Request.Form("tisField")
End If
%>
<%
Dim tisSearch__varDASCB
tisSearch__varDASCB = "null"
If (Request.Form("dasCB") <> "") Then 
  tisSearch__varDASCB = Request.Form("dasCB")
End If
%>
<%
Dim tisSearch__varTISCB
tisSearch__varTISCB = "null"
If (Request.Form("tisCB") <> "") Then 
  tisSearch__varTISCB = Request.Form("tisCB")
End If
%>
<%
Dim tisSearch__varTAPCB
tisSearch__varTAPCB = "null"
If (Request.Form("tapCB") <> "") Then 
  tisSearch__varTAPCB = Request.Form("tapCB")
End If
%>
<%
Dim tisSearch__varATCB
tisSearch__varATCB = "null"
If (Request.Form("atCB") <> "") Then 
  tisSearch__varATCB = Request.Form("atCB")
End If
%>


<%
Dim tisSearch
Dim tisSearch_numRows

Set tisSearch = Server.CreateObject("ADODB.Recordset")
tisSearch.ActiveConnection = MM_connGlobal_STRING
tisSearch.Source = "SELECT DOC_NUMBER, DOC_TYPE, QUALIFICATION,
STATE_QUAL_NUM, URL  FROM TRAINING_DOCS  WHERE ((DOC_TYPE = '" +
Replace(tisSearch__varDASCB, "'", "''") + "') OR (DOC_TYPE = '" +
Replace(tisSearch__varTISCB, "'", "''") + "') OR (DOC_TYPE = '" +
Replace(tisSearch__varTAPCB, "'", "''") + "') OR (DOC_TYPE = '" +
Replace(tisSearch__varATCB, "'", "''") + "'))  AND ((DOC_NUMBER
LIKE '%" + Replace(tisSearch__varTis, "'", "''") + "%') OR (UNIT
LIKE '%" + Replace(tisSearch__varTis, "'", "''") + "%') OR
(QUALIFICATION LIKE '%" + Replace(tisSearch__varTis, "'", "''")
+ "%') OR (STATE_QUAL_NUM LIKE '%" + Replac
(tisSearch__varTis, "'", "''") + "%') OR (NATIONAL_QUAL_NUM LIKE '%"
+ Replace(tisSearch__varTis, "'", "''") + "%') OR (TRAINING_PACKAGE
LIKE '%" + Replace(tisSearch__varTis, "'", "''") + "%') OR (KEYWORD
LIKE '%" + Replace(tisSearch__varTis, "'", "''") + "%'))  ORDER BY
DOC_TYPE"
tisSearch.CursorType = 0
tisSearch.CursorLocation = 2
tisSearch.LockType = 1
tisSearch.Open()

tisSearch_numRows = 0
%>
<--! Here is the form -->

<form action="test.asp" method="post" name="tisForm" id="tisForm">
  <p> 
    <input name="dasCB" type="checkbox" id="dasCB" value="DAS">
    DAS 
    <input name="tisCB" type="checkbox" id="tisCB" value="TIS">
    TIS 
    <input name="tapCB" type="checkbox" id="tapCB" value="TAP">
    TAP 
    <input name="atCB" type="checkbox" id="atCB" value="AT">
    AT</p>
  <p> 
    <input name="tisField" type="text" id="tisField">
    <input type="submit" name="Submit" value="Search">
  </p>
</form>

acdsky · July 19th, 2004, 04:02 PM

Hi

Could u maybe give us some more detail on what the current challenge or problem is?

Dunno if this will help but I normally build the where clause from the user selection. Have a look at this logic:

If CoPrio.Text = "All" Then
        Prio = ""
    Else
        Prio = " And C.Priority" & "=" & "'" & CoPrio & "'"
    End If
    If CoCustID.Text = "All" Then
        CustID = ""
    Else
        CustID = " And C.CustID" & "=" & "'" & CoCustID & "'"
    End If
    If CoCallCat.Text = "All" Then
        CallCat = ""
    Else
        CallCat = " And C.CallCat" & "=" & "'" & CoCallCat & "'"
    End If
    If CoGroup.Text = "All" Then
        Group = ""
    Else
        Group = " And C.GroupName" & "=" & "'" & CoGroup & "'"
    End If
    If Check1.Value = 1 Then
        Q = " WHERE (C.CallStatus <> 'Closed'"
    Else
        Q = " WHERE (C.CallState <> 'On Hold' AND C.CallStatus <> 'Closed'"
    End If

    'Build Query String!
    qryString = Q & Prio & CustID & Group & CallCat & ")"

What I have done here is give the user comboboxes, textboxes and checkboxes with predifined selections but the varables used could be from any txtBox or whatever. I have set a default value as "All" Note this example is vb6 so the .text etc values wont work in asp/vbs so
intead you just need to get them before hand using request.form method as u did.

In the end you will end up with a perfect Where clause in the qryString variable which you could fit into your SQL where needed. Q has been set with the default "Where =" with a static parameter. Its tricky to build the string correctly but has its longterm advantages if the user later want to select his/her own fields and even tables if required later you could use this logic to build the other parts of the select statement including joins etc. It will also Optimise the query.

Hope this helps in some way else gimme some more detail.

Regards
Marnus

SoC · July 19th, 2004, 10:30 PM

Hi acdsky, thanks for your help.

The problem is I haven't had any formal training in SQL or VBScript
and I'm sort of working it out as I go along.

I have a database (just one table) of different documents. I have
worked out how to allow users to search this database with an html
form. This form consists of a text field and 4 checkboxes (each checkbox corresponds to one of four different document types). The idea is that they select one or more checkboxes and then enter a keyword in the text field. This then searches the database. So far, this works fine. However I would like to add a combo box to this form, which would enable the user to filter the search further. Although I worked out how to include the values from the checkboxes as variables in the SQL statement, I'm not sure how to include the values of the combo box.

If you could help me out furtehr it would be great, but please bear in mind that I am a total newbie.

Thanks, SoC

happygv · July 19th, 2004, 10:48 PM

Hi SoC,

What is that the combo box going to have in it? How does that affect the search?

What for those checkboxes used in this search feature?

Can you explain on these to help you better.
Cheers!

_________________________
- Vijay G
Strive for Perfection

SoC · July 20th, 2004, 12:25 AM

The combo box may look something like this:

Code:

<select name="qualSelect" id="qualSelect">
      <option value="cert1">Certificate 1</option>
      <option value="cert2">Certificate 2</option>
      <option value="cert3">Certificate 3</option>
      <option value="cert4">Certificate 4</option>
    </select>

So a user first selects one of the options above, then they select which documents they want using the checkboxes below, then they enter a keyword and click the submit button.

For example, a user selects Certificate 2 from the combo box, DAS and TIS from the checkboxes and enters 'Excel' in the text field. The search will return all DAS and TIS documents which relate to Certificate 2 and contain the word 'Excel'.

The checkboxes look like this:

Code:

<input name="dasCB" type="checkbox" id="dasCB" value="DAS">
    DAS 
    <input name="tisCB" type="checkbox" id="tisCB" value="TIS">
    TIS 
    <input name="tapCB" type="checkbox" id="tapCB" value="TAP">
    TAP 
    <input name="atCB" type="checkbox" id="atCB" value="AT">
    AT

Please also see my initial post for more of the code.

I think I may have to use the Select Case control?

Thanks,
SoC

happygv · July 20th, 2004, 12:47 AM

Hi,

After the user submits, you got to request for qualSelect as you do for textbox and use that in your sql SELECT statement

Code:

str_qualSelect=Request.Form("qualSelect")
...
...
str_sql = "select * from tablename where <your other conditions> and  qualSelectCOLUMNNAME = '" & str_qualSelect & "'"

Hope that helps
Cheers!

_________________________
- Vijay G
Strive for Perfection

SoC · July 20th, 2004, 03:23 AM

Hi,

I think I have sort of got it working, however I have encountered a slightly annoying bug.

When I select an option from the combo box and enter a keyword in the textfield, the search form works.

However, if I do not enter a keyword, I don't get any results, when really I should be getting all of the records which correspond to the combo box value.

For example:
I select Certificate 2 from the combo box and type 'communication' into the text field. I get all the records which include certificate 2 and communication.

If, however, I select Certificate 2 from the combo box but do not enter any keyword in the text field, I don't get any results, whereas I really should be getting all the records which include Certificate 2.

Any ideas, anyone? Really appreciate all your help. Here's the code - I'm just testing it out with the combo box and textfield only at the moment. Later on I'll ad checkboxes etc..

Code:

Variables: 
<%
Dim tisSearch__varCombo
tisSearch__varCombo = "null"
If (Request.Form("qualSelect")    <> "") Then 
  tisSearch__varCombo = Request.Form("qualSelect")   
End If
%>
<%
Dim tisSearch__varTis
tisSearch__varTis = "NOT NULL"
If (Request.Form("tisField")  <> "") Then 
  tisSearch__varTis = Request.Form("tisField") 
End If
%>

Here is the SQL
<%
Dim tisSearch
Dim tisSearch_numRows

Set tisSearch = Server.CreateObject("ADODB.Recordset")
tisSearch.ActiveConnection = MM_connGlobal_STRING
tisSearch.Source = "SELECT UNIT, DOC_NUMBER, DOC_TYPE, QUALIFICATION, STATE_QUAL_NUM, URL  FROM 
TRAINING_DOCS  WHERE (KEYWORD LIKE  '%" + Replace(tisSearch__varTis, "'", "''") + "%' OR 
DOC_TYPE LIKE  '%" + Replace(tisSearch__varTis, "'", "''") + "%' OR 
STATE_QUAL_NUM LIKE '%" + Replace(tisSearch__varTis, "'", "''") + "%' OR 
UNIT LIKE '%" + Replace(tisSearch__varTis, "'", "''") + "%') AND 
QUALIFICATION = '" + Replace(tisSearch__varCombo, "'", "''") + "'"
tisSearch.CursorType = 0
tisSearch.CursorLocation = 2
tisSearch.LockType = 1
tisSearch.Open()

tisSearch_numRows = 0
%>

Thanks
SoC

SoC · July 20th, 2004, 03:29 AM

OK, I think I just found the solution.

I had the default value set to NOT NULL. I simply deleted this, and it seems to work fine now.

For anyone interested in this thread, I'll post up my final code when I'm done, so you can see how it all worked out.

Once again, thanks for all your help, it's much appreciated.

SoC