Search Page ASP

[JP SIngh]

Hi All

We are building an application with ASP/SQL Server and need to build a
search page. I want to display lots of fields/textboxes on the search page
to allow user to search any field by entering the search terms in any of the
textboxes.

I was thinking of using lots of if conditions to build the search string
something like

strSQL = "Select <<fieldnames>> from Contracts where Active= True "

if request.form("contractno") <> "" then
strSQL = strSQL & " and contractno ='" & request.form("contractno")
& "'"
end if

Does anyone know of a better way to build the SQL string. Also any methods
to speed up the query built will be much appreciated. I have already limited
the total results that the query displays but adding " Top 20 " in the SQL
string. Any other suggestions?

 

[Jeff Cochran]

We are building an application with ASP/SQL Server and need to build a
search page. I want to display lots of fields/textboxes on the search page
to allow user to search any field by entering the search terms in any of the
textboxes.

I was thinking of using lots of if conditions to build the search string
something like

strSQL = "Select <<fieldnames>> from Contracts where Active= True "

if request.form("contractno") <> "" then
strSQL = strSQL & " and contractno ='" & request.form("contractno")
& "'"
end if

Does anyone know of a better way to build the SQL string. Also any methods
to speed up the query built will be much appreciated. I have already limited
the total results that the query displays but adding " Top 20 " in the SQL
string. Any other suggestions?

Can't really add any, I know nothing about your application, the data,
the table structures, the location of additional files and so on.
Though any time someone says "I want to display lots of
fields/textboxes" and "I was thinking of using lots of if conditions"
it starts to make me quiver...

Jeff

 

[Bullschmidt]

<<
I was thinking of using lots of if conditions to build the search string
That sounds good to me.

Classic ASP Design Tips - Search Criteria on Multiple Fields
http://www.bullschmidt.com/devtip-searchcriteria.asp

Classic ASP Design Tips - Search For Keywords on Multiple Fields
http://www.bullschmidt.com/devtip-searchmultiplefields.asp

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
Classic ASP Design Tips, ASP Web Database Sample (Freely Downloadable)