ASP vbscript if statement acting strange

Folks, I have this simple little piece of code snippet from a working
code, but the if statement is acting strange. I am new to VBScripting
and ASP, so it might be a really silly question. Can you identify why
the first conditional if statement seems to loose the scope of the
"sql" variable as soon as it comes out of the condition. In the last 3
lines of this code, where I write out the variables, when the first
condition is met, it doesnt write anything.As if the variable doesnt
exist anymore. Whenever the second or the third condition is met, the
response.write writes to screen the sql statement as well as the "cn"
variable. When I have a response.write inside the first condition, it
writes the variable "sql". So variable is being created in the first
condition and somehow is getting flushed out. That doesnt happen for
the 2nd and 3rd conditions. What am I missing here?


************************************************************ ***********************
Dim cn,rs,sql
u_input = trim(request.form("u_input")) 'u_input is the varible for
user input
u_field=request.form("u_field") 'the dropdown varible for user inputted
search field

v_input = trim(request.form("v_input")) 'v_input is the varible for
user input
v_field=request.form("v_field") 'the dropdown varible for user inputted
search field

accessdb="form" ' name of the access db
table_name="User2" ' name of the table within the access db
cn="driver={microsoft access driver (*.mdb)};"
cn=cn & "dbq=" & server.mappath(accessdb)
set rs = server.createobject("ADODB.Recordset")
If NOT(u_input =" ") and v_input = "" then
sql = "select * from "& table_name &" where " & u_field & " LIKE '%%" &
u_input & "%%'"
response.write sql
else if NOT(u_input = "") and NOT(v_input = "") then ' If the user
entered a value query the db
sql = "select * from "& table_name &" where " & u_field & " LIKE '%%" &
u_input & "%%'" & " and " & v_field & " LIKE '%%" & v_input & "%%'"
else ' If the user entered a value query the db
sql = "select * from "& table_name
end if

response.write sql
response.write cn
rs.Open sql, cn
************************************************************ *************************************

Re: ASP vbscript if statement acting strange

On 12 May 2006 11:10:05 -0700, "Jay" wrote:

>Folks, I have this simple little piece of code snippet from a working
>code, but the if statement is acting strange. I am new to VBScripting
>and ASP, so it might be a really silly question. Can you identify why
>the first conditional if statement seems to loose the scope of the
>"sql" variable as soon as it comes out of the condition. In the last 3
>lines of this code, where I write out the variables, when the first
>condition is met, it doesnt write anything.As if the variable doesnt
>exist anymore. Whenever the second or the third condition is met, the
>response.write writes to screen the sql statement as well as the "cn"
>variable. When I have a response.write inside the first condition, it
>writes the variable "sql". So variable is being created in the first
>condition and somehow is getting flushed out. That doesnt happen for
>the 2nd and 3rd conditions. What am I missing here?
>
>
>*********************************************************** ************************
>Dim cn,rs,sql
>u_input = trim(request.form("u_input")) 'u_input is the varible for
>user input
>u_field=request.form("u_field") 'the dropdown varible for user inputted
>search field
>
>v_input = trim(request.form("v_input")) 'v_input is the varible for
>user input
>v_field=request.form("v_field") 'the dropdown varible for user inputted
>search field
>
>accessdb="form" ' name of the access db
>table_name="User2" ' name of the table within the access db
>cn="driver={microsoft access driver (*.mdb)};"
>cn=cn & "dbq=" & server.mappath(accessdb)
>set rs = server.createobject("ADODB.Recordset")
>If NOT(u_input =" ") and v_input = "" then
>sql = "select * from "& table_name &" where " & u_field & " LIKE '%%" &
>u_input & "%%'"
>response.write sql
>else if NOT(u_input = "") and NOT(v_input = "") then ' If the user
>entered a value query the db
>sql = "select * from "& table_name &" where " & u_field & " LIKE '%%" &
>u_input & "%%'" & " and " & v_field & " LIKE '%%" & v_input & "%%'"
>else ' If the user entered a value query the db
>sql = "select * from "& table_name
>end if
>
>response.write sql
>response.write cn
>rs.Open sql, cn
>*********************************************************** **************************************
Try restructuring the code to:

If NOT(u_input =" ") and v_input = ""
then
sql = "select * from "& table_name & " where " & u_field & " LIKE '%%" & u_input & "%%'"
elseif
NOT(u_input = "") and NOT(v_input = "")
then
sql = "select * from " & table_name & " where " & u_field & " LIKE '%%" & u_input & "%%'" & " and " & v_field
& " LIKE '%%" & v_input & "%%'"
else
sql = "select * from "& table_name
end if

response.write(sql)

The response.write in the middle of the If..Then..elseif may cause the problem..

Re: ASP vbscript if statement acting strange

Jay wrote:
> Folks, I have this simple little piece of code snippet from a working
> code, but the if statement is acting strange. I am new to VBScripting
> and ASP, so it might be a really silly question. Can you identify why
> the first conditional if statement seems to loose the scope of the
> "sql" variable as soon as it comes out of the condition. In the last 3
> lines of this code, where I write out the variables, when the first
> condition is met, it doesnt write anything.As if the variable doesnt
> exist anymore. Whenever the second or the third condition is met, the
> response.write writes to screen the sql statement as well as the "cn"
> variable. When I have a response.write inside the first condition, it
> writes the variable "sql". So variable is being created in the first
> condition and somehow is getting flushed out. That doesnt happen for
> the 2nd and 3rd conditions. What am I missing here?
>
>
> ************************************************************ ***********************
> Dim cn,rs,sql
> u_input = trim(request.form("u_input")) 'u_input is the varible for
> user input
> u_field=request.form("u_field") 'the dropdown varible for user
> inputted
> search field
>
> v_input = trim(request.form("v_input")) 'v_input is the varible for
> user input
> v_field=request.form("v_field") 'the dropdown varible for user
> inputted
> search field
>
> accessdb="form" ' name of the access db
> table_name="User2" ' name of the table within the access db
> cn="driver={microsoft access driver (*.mdb)};"
> cn=cn & "dbq=" & server.mappath(accessdb)
> set rs = server.createobject("ADODB.Recordset")
> If NOT(u_input =" ") and v_input = "" then
> sql = "select * from "& table_name &" where " & u_field & " LIKE '%%"
> &
> u_input & "%%'"
> response.write sql

The problem is in this line:
************************************************************ ********
> else if NOT(u_input = "") and NOT(v_input = "") then ' If the user
> entered a value query the db
************************************************************ *************
Either:
a. A line break was left out, in which case your code is missing an "end if"
which should be raising an error (is there an "on error resume next" which
you failed to show us?
or
b. "else if" needs to be one word: "elseif"

I don't know if your code lost its formatting when you pasted it into your
message, but proper indenting will help you catch these things.


> sql = "select * from "& table_name &" where " & u_field & " LIKE '%%"
> &
> u_input & "%%'" & " and " & v_field & " LIKE '%%" & v_input & "%%'"
> else ' If the user entered a value query the db
> sql = "select * from "& table_name
> end if
>
> response.write sql
> response.write cn
> rs.Open sql, cn
> ************************************************************ *************************************

There are other problems:
1. Using the obsolete odbc provider when a perfectly usable Jet OLE DB
provider exists:
http://www.aspfaq.com/show.asp?id=2126
2. Failing to use an explicit connection object, which can disable
connection pooling.3. Failing to validate user inputs before putting them
into the database Combined with the previous tip, your code should look
like:

u_input = trim(request.form("u_input"))
'make sure this contains what it should contain
....
If NOT(u_input =" ") and v_input = "" then
sql= ...
elseif ... then
sql= ...
end if

Or

If NOT(u_input =" ") and v_input = "" then
sql= ...
else
if ... then
sql= ...
else
sql= ...
end if
end if

set cn=createobject("adodb.connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & server.mappath(accessdb)
set rs=cn.execute(sql,,1)

4. Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


HTH,
Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"