Weird ASP/SQL Problem

[cancer10]

Weird ASP/SQL Problem

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

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

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:

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

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.

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

Weird ASP/SQL Problem

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

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

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:

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

[prodigygroup]

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

[wilcora]

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.

[SheSaidImaPregy]

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.

[hopalongcassidy]

Weird ASP/SQL Problem

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

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

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:

<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