Ad:

ASP Discussion Thread
Unsolved
Views: 4181

Mar 6th, 2007

Weird ASP/SQL Problem

Expand Post »

Weird ASP/SQL Problem

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

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:

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:

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?

Similar Threads

ASP/SQL - Problem in ASP
How to connect ASP with sql server ? in Community Introductions
ASP and SQL ? in MS SQL

cancer10

Reputation Points: 58

Solved Threads: 1

Posting Whiz in Training

Offline

234 posts
since Dec 2004

Permalink

May 10th, 2007

Re: Weird ASP/SQL Problem

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.

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
%>

Click to Expand / Collapse Quote originally posted by cancer10 ...

Weird ASP/SQL Problem

Consider the following HTML Form, it has a textbox and a search button.
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:
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:
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?

DataGrl

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since May 2007

Permalink

May 10th, 2007

Re: Weird ASP/SQL Problem

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

prodigygroup

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since May 2007

Permalink

Oct 12th, 2007

Re: Weird ASP/SQL Problem

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.

Rick w

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Oct 2007

Permalink

Oct 20th, 2007

Re: Weird ASP/SQL Problem

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.

SheSaidImaPregy

Reputation Points: 43

Solved Threads: 68

Veteran Poster

Offline

1,080 posts
since Sep 2007

Permalink

Oct 21st, 2007

Re: Weird ASP/SQL Problem

Click to Expand / Collapse Quote originally posted by cancer10 ...

Weird ASP/SQL Problem

Consider the following HTML Form, it has a textbox and a search button.
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:
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:
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

hopalongcassidy

Reputation Points: 53

Solved Threads: 13

Junior Poster

Offline

148 posts
since Oct 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP Forum Timeline: can asp work with mySQL

Next Thread in ASP Forum Timeline: Dynamic Page?

DaniWeb Message

Cancel Changes

User Name
Password