Tutorials

Dynamic 'where' statement

To create a dynamic 'where' statement, you need to grab the information submitted from your search form and then build the relevant statement accordingly.

The code for this tutorial is in ASP/VBScript.

1. Set up a few variables to work with, it makes reading and understanding your code easier (I think!). You will need one variable for each form element that will be passing a value, one to hold the SQL WHERE string and one to hold an Ending value. All will become clear :)

For example:
If you have four form elements, txtName, txtCompany, txtTown and txtCountry - you would need six variables - varName, varCompany, varTown, varCountry, strSQL and strSQLEND.

2. You need to write some code to check those submitted form values so that you only include in your WHERE string, the ones that have a value.

<%
Dim varName, varCompany, varTown, varCounty, strSQL, strSQLEND
varName = Request("txtName")
varCompany = Request("txtCompany")
varTown = Request("txtTown")
varCountry = Request("txtCountry")
strSQL = ""
strSQLEND = "Included = -1"
If varName <> "" Then
strSQL = strSQL & "columnName = '" & varName & "' AND "
Else
strSQL = strSQL
End If
If varCompany <> "" Then
strSQL = strSQL & "columnCompany = '" & varCompany & "' AND "
Else
strSQL = strSQL
End If
If varTown<> "" Then
strSQL = strSQL & "columnTown = '" & varTown & "' AND "
Else
strSQL = strSQL
End If
If varCountry <> "" Then
strSQL = strSQL & "columnCountry = '" & varCountry & "' AND "
Else
strSQL = strSQL
End If
If strSQL = "" Then
strSQL = strSQLEND
Else
strSQL = strSQL & " " & strSQLEND
End If
%>

To explain...

What you have done in the code above is check each form element for a value, if it has one, it has it's relevant SQL added into the WHERE statement (held in the variable called strSQL).

If, at the end of checking all the form elements, strSQL is empty, we pass it a value that will return all records from the database. (In the Access database, there will be a Yes/No column signifying that each record should be included. If it is checked (-1) then it will be returned.)

Regardless of wether or not strSQL contains a value, we add the strSQLEND value on to the end of the string - this will make a valid WHERE string.

3. Now you need to add that dynamically built WHERE statement onto the end of your recordset Select statement.

Find this line of your recordset code...

rsName.Source = "SELECT * FROM tableName"

And change it to this...

rsName.Source = "SELECT * FROM tableName WHERE " & strSQL

Notes

rsName is the name of your recordset - you should already have a basic recordset set up on your page before you start this...by basic, I mean - Select * from table.
tableName is the name of your table in your database.
columnName, columnCompany, columnTown and columnCountry are all column names in the database.

The SQL WHERE statement this builds is very rigid. It will only return exact matches because of the Operators I have used.

You can make it more flexible by changing = to LIKE and changing AND to OR when building the SQL statement, should you wish.

Dreamweaver

Database

RSS Feed Eater

Tutorials

Dynamic 'where' statement

To explain...

Notes