Create an SQL string from selected form items

[davidharveyg]

Hi,

I have an asp form with several search fields available.
Each search field has a check box against it, i.e. the user can decide
which fields should be included in the search.
Some fields are text boxes, others are drop downs.

I want to know how to create a simple SQL search string based on the
fields the user selects (or rather based on the check boxes the users
selects and the data in those fields to search on.

I have the following in my ASP code

customer = request.form("CustomerName") ...user can select from a list
date_s = request.form("start") .... user enters a date mm/dd/yyyy
date_e = request.form("end") .... user enters a date mm/dd/yyyy
product = request.form("prod") .... user enters a code or part of a
code
serial = request.form("serial") .... user enters a number or part of a
number
fr = request.form("Fault-R") .... user enters a string to find
matching
ff = request.form("Fault-F") .... user selects from a drop down
fn = request.form("Fault-N").... user enters a string to find matching
ca = request.form("Cause-F").... user selects from a drop down
cn = request.form("Cause-N").... user enters a string to find matching


Obviously the user could select any combination of fields, so what is
the best way of handling this ?

Appreciate your help / advice

Thanks

David

 

[Bullschmidt]

Perhaps this may hopefully give you some ideas:

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

Example on a page receiving a post of a SQL statement based on two
listboxes (Rep and Customer) that might each have a value of "" (i.e.
blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the
results since it is always true) as the SQL statement possibly may use
AND with criteria after it or possibly the SQL statement may not have
anything after it. For example a SQL statement without the (1=1) like
this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

 

[Anthony Jones]

Perhaps this may hopefully give you some ideas:

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

Example on a page receiving a post of a SQL statement based on two
listboxes (Rep and Customer) that might each have a value of "" (i.e.
blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the
results since it is always true) as the SQL statement possibly may use
AND with criteria after it or possibly the SQL statement may not have
anything after it. For example a SQL statement without the (1=1) like
this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

How are the from/to date types handle here?

What happens when the user enters this criteria for Rep:-

Fred'); Drop myTable; --

??

You should read up on SQL injection attacks.

The reason why this question hasn't been answered yet is doing this properly
takes much more effort.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com

Having read up on SQL Injection attacks I suggest you review your previous
freelance work and provide any fixes necessary to your customers.

 

[scened]

Perhaps this may hopefully give you some ideas:

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

Example on a page receiving a post of a SQL statement based on two
listboxes (Rep and Customer) that might each have a value of "" (i.e.
blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
 strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
 strSQL = strSQL & " AND (Customer='" & Customer  & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the
results since it is always true) as the SQL statement possibly may use
AND with criteria after it or possibly the SQL statement may not have
anything after it.  For example a SQL statement without the (1=1) like
this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

Best regards,
J. Paul Schmidt, Freelance Web and Database Developerhttp://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

*** Sent via Developersdexhttp://www.developersdex.com***

_____________________________

Many thanks J. Paul Schmidt,

This method worked perfectly, although I had found an example of it a
couple of days ago.
Appreciate you taking the time to reply to my post

Regards

David Gordon.