Problem with Select

[desiree]

Hi !

Can someone please help me!

I am trying to write a select string with all the "WHERE" value, coming from a form. I have read a large number of tutorials about SQL, but they all talking about values that are predetermined.

This is what I have done so far:

------------------------------------
Set RecSet = Server.CreateObject("ADODB.Recordset")

ShowObjects = "SELECT * FROM objects WHERE district='" & Request.Form("district") & "'"

RecSet.Open ShowObjects, Connect
---------------------------------------

When I try to add more filters (from the form), I get all kind of syntax errors.

Can anyone help me with this ??
Can anyone tell me where to find a tutorial about this ??

Desiree

[skhanal]

You have to use AND clause to add more filter conditions in your WHERE clause. The example you have shows only one condition

WHERE district = Request.Form("district")

you may need (assuming your are trying to filter by state)

WHERE district = Request.Form("district") AND
state = Request.Form("state")

[desiree]

Thanks skhanal, for your reply.

I know that, but I dont know how to do it.

For example, if I use:

-----------------
ShowObjects = "SELECT * FROM objects WHERE district = Request.Form("district") AND type = Request.Form("type")
-------------------------

I get the error:

------------------------
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/search_results.asp, line 29
---------------------------

Desiree

[kthwoolner]

I am going to assume that one or many fields but not all may be filled and provide the parameters for a search.

How about build a sp with optional parameters - default value null - one for each field on the form?

Declare
@parm1 datatype =null
@parm2 datatype =null
@parm2 datatype =null
....
@parmn datatype =null


Select [Select List] from [Table] where
(@parm1 = fieldname1 or is null)
and
(@parm2 = fieldname2 or is null)
and
(@parm3 = fieldname3 or is null)

..........
(@parmn = fieldnamen or is null)

Execute the sp supplying values for any parameter known.

If no parameter values are provided then all the records are returned. The number of records returned will decrease the more parameters are provided.



I have never seen a sp built in this way in the books but have used it in many MSAccess/SQLServer solutions myself and it works.
I call it Optional Parameters.
It turns the structure of parameter usage on it's head and some techie may turn round and say there is a performance hit.
I work with college student records and have to provide forms with many search fields, some of which are known and some not. It saves building query strings on the fly which can be a real pain.

Hope it helps