Hello,

I am having trouble conducting a search on a database. I want to have a

form that submits a  text box and a drop down menu for the search

criteria, I want to allow a search for either a state, city or name for

the dropdown menu. So far I have come up with the following code, but I

cannot get it to work right. I can do it by only searching for one

parameter, but I would like to add the second parameter. Here is the code:



Dim strField, strField1, strCriteria

strField = Request.Form("Field")

strField1 = Request.Form("Field1")



Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "Gym", objConn, adOpenStatic, adLockReadOnly, adCmdTable

strSQL = "SELECT strField AND strField2 FROM Gym ;"



strCriteria = strField

objRS.Filter strCriteria





If objRS.EOF Then

  Response.Write "No gyms found"

 Else

  

Thanks in advance,

Bob F.

It would be a lot better (faster) to filter out the records you want on the 

database, instead of in the recordset.



Try this



Dim strField, strField1, strCriteria

strField = Request.Form("Field")

strField1 = Request.Form("Field1")



if not strField = "" then

         ' people want to search on strField with it's value

         strCriteria = " strField = '" & strField & "'"

else

         ' asume field1 is filled

         strCriteria = " strField1 = '" & strField1 & "'"

end if



strSQL = "SELECT strField, strField2 FROM Gym WHERE" & strCriteria





Suppose strField represents a city, and Request.Form("Field") contains Boston,

your SQL statement will look like this:



SELECT strField, strField1 FROM Gym WHERE strField = 'Boston'



This code looks a little odd because you have chosen to name the input 

boxes after your column names



It is recommended to change the names of the columns of your database.

strField and strField2 are not very descriptive.



If you say: SELECT clCity, clCountry, clAddress FROM tblGym

or something like that, it's obvious what you are pulling from where.......







HtH



Imar





At 08:05 AM 9/21/2000 -0700, you wrote:

>Hello,

>I am having trouble conducting a search on a database. I want to have a

>form that submits a  text box and a drop down menu for the search

>criteria, I want to allow a search for either a state, city or name for

>the dropdown menu. So far I have come up with the following code, but I

>cannot get it to work right. I can do it by only searching for one

>parameter, but I would like to add the second parameter. Here is the code:

>

>Dim strField, strField1, strCriteria

>strField = Request.Form("Field")

>strField1 = Request.Form("Field1")

>

>Dim objRS

>Set objRS = Server.CreateObject("ADODB.Recordset")

>objRS.Open "Gym", objConn, adOpenStatic, adLockReadOnly, adCmdTable

>strSQL = "SELECT strField AND strField2 FROM Gym ;"

>

>strCriteria = strField

>objRS.Filter strCriteria

>

>

>If objRS.EOF Then

>   Response.Write "No gyms found"

>  Else

>

>Thanks in advance,

>Bob F.

>

Bob, 



1. This line is redundant: 

      strSQL = "SELECT strField AND strField2 FROM Gym ;"

...cos you never use the value of strSQL that you've defined.  (It's also

syntactically incorrect as a SQL statement)



2. The recordset's Filter interface is a PROPERTY, not a METHOD.  In order

to use .Filter, you must assign a value to it. Some examples: 

    objRS.Filter = "vc_Surname = 'Smith' "    ' Hide all records except

where the person's surname is 'Smith'

    objRS.Filter = "int_Age <= 21 "               ' Hide all records except

where the person's age is 21 or less

    objRS.Filter = "vc_Surname = 'Smith' AND int_Age < 21 "          ' Hide

all records except under-21s called 'Smith' 

And if you want to use strCriteria:

    strCriteria = "vc_Surname = 'Smith' AND int_Age < 21 "

    objRS.Filter = strCriteria



HTH, Ian







-----Original Message-----

From: Bob [mailto:bobage24@a...]

Sent: Thursday, September 21, 2000 4:05 PM

To: ASP Databases

Subject: [asp_databases] Searching a database, can't figure this out.





Hello,

I am having trouble conducting a search on a database. I want to have a

form that submits a  text box and a drop down menu for the search

criteria, I want to allow a search for either a state, city or name for

the dropdown menu. So far I have come up with the following code, but I

cannot get it to work right. I can do it by only searching for one

parameter, but I would like to add the second parameter. Here is the code:



Dim strField, strField1, strCriteria

strField = Request.Form("Field")

strField1 = Request.Form("Field1")



Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "Gym", objConn, adOpenStatic, adLockReadOnly, adCmdTable

strSQL = "SELECT strField AND strField2 FROM Gym ;"



strCriteria = strField

objRS.Filter strCriteria





If objRS.EOF Then

  Response.Write "No gyms found"

 Else

  

Thanks in advance,

Bob F.