Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 18th, 2004, 07:31 PM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using values from a combo box as variables in SQL

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>
 
Old July 19th, 2004, 04:02 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default

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
 
Old July 19th, 2004, 10:30 PM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 19th, 2004, 10:48 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 20th, 2004, 12:25 AM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old July 20th, 2004, 12:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 20th, 2004, 03:23 AM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 20th, 2004, 03:29 AM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
values in combo box Vince_421 Access VBA 8 May 10th, 2007 08:46 AM
combo box saves Number of Values JpaulH Access VBA 2 May 3rd, 2006 06:55 AM
passing variables through a combo box ozPATT Access VBA 3 January 13th, 2006 09:21 AM
Testing for NULL Values in a Combo Box Aaron Edwards Access 2 September 28th, 2005 09:37 AM
Combo box with different display and send values iniro VB.NET 2002/2003 Basics 3 November 9th, 2004 12:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.