DaniWeb Forum Index > Web Development > ASP

Weird ASP/SQL Problem

Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Dec 2004

Posts: 234

Reputation: cancer10 is an unknown quantity at this point

Solved Threads: 0

cancer10

Offline

Posting Whiz in Training

Weird ASP/SQL Problem

Mar 6th, 2007

Weird ASP/SQL Problem

Consider the following HTML Form, it has a textbox and a search button.

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
<form method=post action="search.asp">
<input name="txtName" type="text" id="txtEmail" />
<input type="submit" name="Submit3" value="Search" />
</form>
<form method=post action="search.asp">
<input name="txtName" type="text" id="txtEmail" />
<input type="submit" name="Submit3" value="Search" />
</form>

The search.asp page:

Help with Code Tags

ASP Syntax (Toggle Plain Text)

select * from tbl_group_contact where userName ='"& request("txtName") &"'

if I put this query in the search.asp page then it will search by name based on the name I entered in the textbox. But if I leave the textbox empty and click on the search button, SQL fetches all the rows from the table. If you havent noticed this before, try it now!!

Well, this is not my problem/question. my question is if I place a combo box instead of the textbox and assign a NULL value to one of the option field like:

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
<select name="txtType" id="txtType">
<option value="" selected="selected">All</option>
</select>
<select name="txtType" id="txtType">
<option value="" selected="selected">All</option>
</select>

and press the search button, then it does not fetch all the rows as it did for the empty textbox.

Any idea why?

FocusInterview.com - PHP Interview Questions & Answers

IGCHosting - Cheapest Web Hosting

•

Join Date: May 2007

Posts: 2

Reputation: DataGrl is an unknown quantity at this point

Solved Threads: 0

DataGrl

Offline

Newbie Poster

Re: Weird ASP/SQL Problem

May 10th, 2007

I think you may be experiencing a discrepancy between empty strings and null values. An empty string does not have the same value as Null. On your results page, try doing a determination of the value it actually receives before trying to process it. You may find that your form is not sending the value you think it is.

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
<%
dim varTest
varTest = request.form("txtName")
Select Case varTest
    Case NULL
        response.write "I am NULL"
    Case ""
        response.write "I am an empty string"
    Case else
        response.write varTest
END SELECT
%>
<%
dim varTest
varTest = request.form("txtName")
Select Case varTest
    Case NULL
        response.write "I am NULL"
    Case ""
        response.write "I am an empty string"
    Case else
        response.write varTest
END SELECT
%>

•

Originally Posted by cancer10

Weird ASP/SQL Problem

Consider the following HTML Form, it has a textbox and a search button.
Help with Code Tags

ASP Syntax (Toggle Plain Text)
<form method=post action="search.asp">
<input name="txtName" type="text" id="txtEmail" />
<input type="submit" name="Submit3" value="Search" />
</form>
<form method=post action="search.asp"> <input name="txtName" type="text" id="txtEmail" /> <input type="submit" name="Submit3" value="Search" /> </form>
The search.asp page:
Help with Code Tags

ASP Syntax (Toggle Plain Text)
select * from tbl_group_contact where userName ='"& request("txtName") &"'
select * from tbl_group_contact where userName ='"& request("txtName") &"'
if I put this query in the search.asp page then it will search by name based on the name I entered in the textbox. But if I leave the textbox empty and click on the search button, SQL fetches all the rows from the table. If you havent noticed this before, try it now!!

Well, this is not my problem/question. my question is if I place a combo box instead of the textbox and assign a NULL value to one of the option field like:
Help with Code Tags

ASP Syntax (Toggle Plain Text)
<select name="txtType" id="txtType">
<option value="" selected="selected">All</option>
</select>
<select name="txtType" id="txtType"> <option value="" selected="selected">All</option> </select>
and press the search button, then it does not fetch all the rows as it did for the empty textbox.

Any idea why?

•

Join Date: May 2007

Posts: 7

Reputation: prodigygroup is an unknown quantity at this point

Solved Threads: 0

prodigygroup prodigygroup is offline

Offline

Newbie Poster

Re: Weird ASP/SQL Problem

May 10th, 2007

Thats right.
The textbox must have been passing NULL while your select is passing a blank string.

•

Join Date: Oct 2007

Posts: 3

Reputation: Rick w is an unknown quantity at this point

Solved Threads: 0

Rick w

Offline

Newbie Poster

Re: Weird ASP/SQL Problem

Oct 12th, 2007

The proper why to make this call is by using a parameterized query. Otherwise, you are leaving yourself open to sql injection and the problem you are seeing. Sql server views all parameters as literals.

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Weird ASP/SQL Problem

Oct 20th, 2007

look at how it looks on sql when the server reads it..

through the textbox with no value:
select * from tbl_group_contact where userName ='NULL'
therefore it will select all usernames as there is no value to compare it against. So it compares nothing and selects everything

through select with no value:
select * from tbl_group_contact where userName =''
therefore it compares username to "".

The difference is that SQL understands NULL as an empty field and understands "" as a comparison.

The null will select everything and the "" will select only those fields where username does not equal null but does not have any information stored.

This is why validation is so detrimental in programming. A simple if isNull(Request.QueryString("txtName")) or Request.QueryString("txtName") = "" then statement can stop the SQL from firing and just show an error instead of tapping the database and getting distorted results.

Last edited by SheSaidImaPregy; Oct 20th, 2007 at 6:56 pm.

•

Join Date: Oct 2007

Posts: 147

Reputation: hopalongcassidy is an unknown quantity at this point

Solved Threads: 13

hopalongcassidy hopalongcassidy is offline

Offline

Junior Poster

Re: Weird ASP/SQL Problem

Oct 21st, 2007

•

Originally Posted by cancer10

Weird ASP/SQL Problem

Consider the following HTML Form, it has a textbox and a search button.
Help with Code Tags

ASP Syntax (Toggle Plain Text)
<form method=post action="search.asp">
<input name="txtName" type="text" id="txtEmail" />
<input type="submit" name="Submit3" value="Search" />
</form>
<form method=post action="search.asp"> <input name="txtName" type="text" id="txtEmail" /> <input type="submit" name="Submit3" value="Search" /> </form>
The search.asp page:
Help with Code Tags

ASP Syntax (Toggle Plain Text)
select * from tbl_group_contact where userName ='"& request("txtName") &"'
select * from tbl_group_contact where userName ='"& request("txtName") &"'
if I put this query in the search.asp page then it will search by name based on the name I entered in the textbox. But if I leave the textbox empty and click on the search button, SQL fetches all the rows from the table. If you havent noticed this before, try it now!!

Well, this is not my problem/question. my question is if I place a combo box instead of the textbox and assign a NULL value to one of the option field like:
Help with Code Tags

ASP Syntax (Toggle Plain Text)
<select name="txtType" id="txtType">
<option value="" selected="selected">All</option>
</select>
<select name="txtType" id="txtType"> <option value="" selected="selected">All</option> </select>
and press the search button, then it does not fetch all the rows as it did for the empty textbox.

Any idea why?

I noticed that your text control had what amount to two different names!
"<input name="txtName" type="text" id="txtEmail" />"
"name" and "id" are supposed to mean the same thing! "id" is supposed to be the preferred parameter. "name" will probably be deprecated eventually.
When your browser transmitted the value of the textbox, it transmitted it under the id, "txtEmail" (because, either , it encountered "id" after "name" when parsing the form or it simply uses "id" whenever "id" is specified and uses "name" otherwise ).
You retrieved it under the name "txtName" which did not exist. I think that is why you got a NULL. When you did it with an <option> tag, you transmitted an empty string ("") under the id "txtType" (you used "txtType" for "name" and "id").

Hoppy