Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Create an SQL string from selected form items

Reply
Thread Tools

Create an SQL string from selected form items

 
 
davidharveyg@yahoo.co.uk
Guest
Posts: n/a
 
      12-09-2008
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


 
Reply With Quote
 
 
 
 
Bullschmidt
Guest
Posts: n/a
 
      12-11-2008
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

*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
 
Anthony Jones
Guest
Posts: n/a
 
      12-11-2008
"Bullschmidt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

--
Anthony Jones - MVP ASP/ASP.NET


 
Reply With Quote
 
scened@gmail.com
Guest
Posts: n/a
 
      12-12-2008
On 11 Dec, 04:12, Bullschmidt <(E-Mail Removed)> wrote:
> 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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DropDownList 2 always returns Selected = 0 for all items - even selected item Iain ASP .Net 3 12-11-2006 11:07 AM
Looping through CheckBoxList items doesn't pick up selected ones when items were disabled Bart Van Hemelen ASP .Net 2 09-04-2006 08:35 AM
How to create session objects for all selected items of a listbox? antonyliu2002@yahoo.com ASP .Net 0 09-26-2005 06:40 PM
How to get the selected items from a user control in a web form Celine ASP .Net 3 05-10-2004 11:43 AM
Deselect any selected items in a select form field with multiple attribute? Keiron Waites Javascript 1 10-21-2003 10:24 AM



Advertisments