Problem with Response.Request()

Hi all,

Something really odd is happening on one of my asp pages. The page causing
the trouble is being called by a form and it is in charge of inserting
values on an Access DB. After inserting the values I want the page to
redirect the user to the input page where the form resides so the users can
keep adding info to the DB. In order to do that I use a plain-old
Response.redirect("request.asp") ... buuuuuuuut... it redirects the user to
the main page index.asp.

The code is as follows:

------------------------------------------------------------ ------------------------------------------------------------ --------
<%@ LANGUAGE = VBScript %>


<%
Dim sUser
Dim sPass
Dim bFlag

Dim intUserID
Dim intTypeID
Dim intYear
Dim intMonth
Dim intDay
Dim strQty

Session("ErrID") = 0

intUserID = request.form("txtUser")
intTypeID = request.form("CboType")
intYear = Mid(request.form("txtDate"),7,4) 'Year(request.form("txtDate"))
intMonth = Mid(request.form("txtDate"),4,2) 'Month(request.form("txtDate"))
intDay = Mid(request.form("txtDate"),1,2)'(Day(request.form("txtDate" ))
strDetails = request.form("txtDetails")

'Check if there is any matches for the user and date and type 1, 6, 7 or 8
Set objRst= objConn.Execute("SELECT TblMain.MaiID, TblMain.MaiUserID,
TblMain.MaiYear, TblMain.MaiMonth, TblMain.MaiDay, TblMain.MaiType FROM
TblMain WHERE (((TblMain.MaiUserID)=" & intUserID & ") AND
((TblMain.MaiYear)=" & intYear & ") AND ((TblMain.MaiMonth)=" & intMonth &
") AND ((TblMain.MaiDay)=" & intDay & ") AND ((TblMain.MaiType)=1 Or
(TblMain.MaiType)=6 Or (TblMain.MaiType)=7 Or (TblMain.MaiType)=8));")

'If there are no matches insert entry
If objrst.eof= true then
Select Case intTypeID
Case 1 'Day of holidays
intTypeID = 11
intTypeIDReq = 1
Case 6 'Half day of holidays
intTypeID = 12
intTypeIDReq = 6
Case 7 'Half day off (allowed)
intTypeID = 13
intTypeIDReq = 7
Case 8 'Day off (allowed)
intTypeID = 14
intTypeIDReq = 8
End Select

strQty = "0"

Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.ConnectionString = connString
objConn2.Properties("Jet OLEDB:Database Password") = "xxxxxxxxxx"
objConn2.Open()
objConn2.CursorLocation=3 'adUseClient

Set objRst2 = Server.CreateObject("ADODB.Recordset")

If isnull(strDetails) or strDetails = "" Then
strDetails = " "
End If

Set objRst2= objConn2.Execute("INSERT INTO TblPending ( PenUserID,
PeniYear, PenMonth, PenDay, PenQty, PenType, PenReason, PenDateReq ) SELECT
" & intUserID & " AS Expr1, " & intYear & " AS Expr2, " &intMonth & " AS
Expr3, " & intDay & " AS Expr4, '" & strQty & "' AS Expr5, " & intTypeID & "
AS Expr6, ' " & strDetails & "' AS Expr7, #" & Now() & "# AS Expr8;")
Set objRst2= objConn2.Execute("INSERT INTO TblMain ( MaiUserID, MaiYear,
MaiMonth, MaiDay, MaiQty, MaiType, MaiTypeRequested ) SELECT " & intUserID &
" AS Expr1, " & intYear & " AS Expr2, " & intMonth & " AS Expr3, " & intDay
& " AS Expr4, '" & strQty & "' AS Expr5, " & intTypeID & " AS Expr6, " &
intTypeIDReq & " AS Expr7;")

objConn2.Close

objRst.Close
objConn.Close

Response.redirect("request.asp")

End If

%>
------------------------------------------------------------ ------------------------------------------------------------ --------

Any ideas?

TY

Marc

Re: Problem with Response.Request()

Marc Llenas wrote:
> Hi all,
>
> Something really odd is happening on one of my asp pages. The page
> causing the trouble is being called by a form and it is in charge of
> inserting
> values on an Access DB. After inserting the values I want the page to
> redirect the user to the input page where the form resides so the
> users can keep adding info to the DB. In order to do that I use a
> plain-old
> Response.redirect("request.asp") ... buuuuuuuut... it redirects the
> user to the main page index.asp.
>
> The code is as follows:

1. start with some simple debugging: Comment out any redirect statements and
insert:

Response.Write "Redirecting to " & pointed>

Oh, wait, I just noticed that your code only has a single redirect
statement, so skip to step 2.


2. Verify that the expected redirect statement is running. If so, put the
redirect statement at the beginning of your page and run it. Verify that it
redirects to the expected page. If not, do you have any code in request.asp
that redirects to index.asp? If so, it may be firing ...

Some general comments about your code:

1. Why aren't you closing and destroying objConn and objRst as soon as you
are done with them? I.E. why wait until the end of the page? These are
expensive resources that should be freed up as soon as possible: in this
case, immediately after the "If objrst.eof= true then...end if" block

1a. There is no need to create a second Connection object: simply close
objConn, set its properties to the new settings, and open it.

2. Don't use dynamic sql - it leaves you vulnerable to sql injection
attacks:
http://mvp.unixwiz.net/techtips/sql-injection.html

Instead, use saved parameter queries:
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

Or, if you are dead-set against using saved queries, use a Command object to
parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

3. To facilitate debugging, and make your code a little more readable, you
should assign your sql statements to string variables which you use in your
Execute statements:

dim sSQL
sSQL = "SELECT ... "
'for debugging - comment out when everything is working:
Response.Write sSQL
set rs=objConn.Execute(sSQL,,1)

4. Tell ADO what the CommandType is - making it guess can lead to rare
unexpected errors. Do this by using the third argument in the Execute
statement. In the above example, 1 corresponds to adCmdText, which tells ADO
to expect a sql statement passed as a string (text).

5. Don't use an expensive recordset to execute a statement that will not
return records (an action query, aka DML, Data Modification Language).
Instead, simply use the Execute statement:

-------------
Dim sSQL
sSQL = "INSERT ... "
'for debugging - comment out when everything is working:
Response.Write sSQL
....
objConn.Execute sSQL,,129
---------------

129 is the combination of two constants: 1 (adCmdText) and 128
(adExecuteNoRecords)

You need to tell ADO that your query will not return records so that it will
not create a recordset object behind the scenes.



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"

Re: Problem with Response.Request()

Bob, Thanks a million for your comments.

Be asured I will optimize my code based on them.

Cheers,

Marc

"Bob Barrows [MVP]" escribió en el mensaje
news:%23kNejilzFHA.1132@TK2MSFTNGP10.phx.gbl...
> Marc Llenas wrote:
>> Hi all,
>>
>> Something really odd is happening on one of my asp pages. The page
>> causing the trouble is being called by a form and it is in charge of
>> inserting
>> values on an Access DB. After inserting the values I want the page to
>> redirect the user to the input page where the form resides so the
>> users can keep adding info to the DB. In order to do that I use a
>> plain-old
>> Response.redirect("request.asp") ... buuuuuuuut... it redirects the
>> user to the main page index.asp.
>>
>> The code is as follows:
>
> 1. start with some simple debugging: Comment out any redirect statements
> and insert:
>
> Response.Write "Redirecting to " & > statement pointed>
>
> Oh, wait, I just noticed that your code only has a single redirect
> statement, so skip to step 2.
>
>
> 2. Verify that the expected redirect statement is running. If so, put the
> redirect statement at the beginning of your page and run it. Verify that
> it redirects to the expected page. If not, do you have any code in
> request.asp that redirects to index.asp? If so, it may be firing ...
>
> Some general comments about your code:
>
> 1. Why aren't you closing and destroying objConn and objRst as soon as you
> are done with them? I.E. why wait until the end of the page? These are
> expensive resources that should be freed up as soon as possible: in this
> case, immediately after the "If objrst.eof= true then...end if" block
>
> 1a. There is no need to create a second Connection object: simply close
> objConn, set its properties to the new settings, and open it.
>
> 2. Don't use dynamic sql - it leaves you vulnerable to sql injection
> attacks:
> http://mvp.unixwiz.net/techtips/sql-injection.html
>
> Instead, use saved parameter queries:
> 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
>
> Or, if you are dead-set against using saved queries, use a Command object
> to parameterize CommandText:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> 3. To facilitate debugging, and make your code a little more readable, you
> should assign your sql statements to string variables which you use in
> your Execute statements:
>
> dim sSQL
> sSQL = "SELECT ... "
> 'for debugging - comment out when everything is working:
> Response.Write sSQL
> set rs=objConn.Execute(sSQL,,1)
>
> 4. Tell ADO what the CommandType is - making it guess can lead to rare
> unexpected errors. Do this by using the third argument in the Execute
> statement. In the above example, 1 corresponds to adCmdText, which tells
> ADO to expect a sql statement passed as a string (text).
>
> 5. Don't use an expensive recordset to execute a statement that will not
> return records (an action query, aka DML, Data Modification Language).
> Instead, simply use the Execute statement:
>
> -------------
> Dim sSQL
> sSQL = "INSERT ... "
> 'for debugging - comment out when everything is working:
> Response.Write sSQL
> ...
> objConn.Execute sSQL,,129
> ---------------
>
> 129 is the combination of two constants: 1 (adCmdText) and 128
> (adExecuteNoRecords)
>
> You need to tell ADO that your query will not return records so that it
> will not create a recordset object behind the scenes.
>
>
>
> 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"
>

Re: Problem with Response.Request()

Actually Bob, one more question about creation/destruction of conn & rst.

Because the application has several ASP pages, all of them working with rst,
I'm currently using an include that takes care of creating the connection
and the rst.. Based on your comments, I was wondering if that is resource
cost efficient or if I´d be better off manually creating the conn and the
rst every time I need them.

Thanks,

Marc


"Bob Barrows [MVP]" escribió en el mensaje
news:%23kNejilzFHA.1132@TK2MSFTNGP10.phx.gbl...
> Marc Llenas wrote:
>> Hi all,
>>
>> Something really odd is happening on one of my asp pages. The page
>> causing the trouble is being called by a form and it is in charge of
>> inserting
>> values on an Access DB. After inserting the values I want the page to
>> redirect the user to the input page where the form resides so the
>> users can keep adding info to the DB. In order to do that I use a
>> plain-old
>> Response.redirect("request.asp") ... buuuuuuuut... it redirects the
>> user to the main page index.asp.
>>
>> The code is as follows:
>
> 1. start with some simple debugging: Comment out any redirect statements
> and insert:
>
> Response.Write "Redirecting to " & > statement pointed>
>
> Oh, wait, I just noticed that your code only has a single redirect
> statement, so skip to step 2.
>
>
> 2. Verify that the expected redirect statement is running. If so, put the
> redirect statement at the beginning of your page and run it. Verify that
> it redirects to the expected page. If not, do you have any code in
> request.asp that redirects to index.asp? If so, it may be firing ...
>
> Some general comments about your code:
>
> 1. Why aren't you closing and destroying objConn and objRst as soon as you
> are done with them? I.E. why wait until the end of the page? These are
> expensive resources that should be freed up as soon as possible: in this
> case, immediately after the "If objrst.eof= true then...end if" block
>
> 1a. There is no need to create a second Connection object: simply close
> objConn, set its properties to the new settings, and open it.
>
> 2. Don't use dynamic sql - it leaves you vulnerable to sql injection
> attacks:
> http://mvp.unixwiz.net/techtips/sql-injection.html
>
> Instead, use saved parameter queries:
> 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
>
> Or, if you are dead-set against using saved queries, use a Command object
> to parameterize CommandText:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> 3. To facilitate debugging, and make your code a little more readable, you
> should assign your sql statements to string variables which you use in
> your Execute statements:
>
> dim sSQL
> sSQL = "SELECT ... "
> 'for debugging - comment out when everything is working:
> Response.Write sSQL
> set rs=objConn.Execute(sSQL,,1)
>
> 4. Tell ADO what the CommandType is - making it guess can lead to rare
> unexpected errors. Do this by using the third argument in the Execute
> statement. In the above example, 1 corresponds to adCmdText, which tells
> ADO to expect a sql statement passed as a string (text).
>
> 5. Don't use an expensive recordset to execute a statement that will not
> return records (an action query, aka DML, Data Modification Language).
> Instead, simply use the Execute statement:
>
> -------------
> Dim sSQL
> sSQL = "INSERT ... "
> 'for debugging - comment out when everything is working:
> Response.Write sSQL
> ...
> objConn.Execute sSQL,,129
> ---------------
>
> 129 is the combination of two constants: 1 (adCmdText) and 128
> (adExecuteNoRecords)
>
> You need to tell ADO that your query will not return records so that it
> will not create a recordset object behind the scenes.
>
>
>
> 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"
>

Re: Problem with Response.Request()

Here is how I do it:
my include file contains a function that returns an open connection object.

<%
dim connectstring
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0; ..."
function GetConnection()
dim cn
set =createobject("adodb.connection")
cn.connectstring = connectstring
cn.Open
Set GetConnection = cn

end function
%>

In your case, you might add a parameter to this function to specify which
connection to return.
You can add a sub to close and destroy a connection as well.

In my page, I do not call that function until immediately before I'm ready
to use the connection. I take care of all the upfront work first (data
validation, building sql statements, etc) then, if all is well, I call the
function. That way, I'm not needlessly opening a connection if I don't need
to.

Also, since you are using Execute to run the SELECT statement, there is no
need to instantiate a recordset object prior to running the Execute. In
fact, it's a waste of time: the Execute statement creates its own recordset
object behind the scenes. That recordset is returned, and, when a "set var
=" statement was used to call the Execute statement, the recordset is
assigned to var, replacing whatever var already contained, even if it
contained nothing.

Bob Barrows
PS. Any progress on the actual problem in your OP?)

Marc Llenas wrote:
> Actually Bob, one more question about creation/destruction of conn &
> rst.
>
> Because the application has several ASP pages, all of them working
> with rst, I'm currently using an include that takes care of creating
> the connection and the rst.. Based on your comments, I was wondering
> if that is resource cost efficient or if I´d be better off manually
> creating the conn and the rst every time I need them.
>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with Response.Request()

Bob thanks for the tip on the include. Will implement that asap.

About the original problem, still no luck. If I move the Response.Request to
the beginning it works.

-------------------------------------------------

<%@ LANGUAGE = VBScript %>



<%

Response.redirect ("user_wish.asp")

------------------------------------------------

Anywhere past the beginning it redirects to index.asp and there is no other
redirect statements in the code... Very very strange.

Will keep investigating.

Marc

"Bob Barrows [MVP]" escribió en el mensaje
news:uDAd8knzFHA.2064@TK2MSFTNGP09.phx.gbl...
> Here is how I do it:
> my include file contains a function that returns an open connection
> object.
>
> <%
> dim connectstring
> connectstring = "Provider=Microsoft.Jet.OLEDB.4.0; ..."
> function GetConnection()
> dim cn
> set =createobject("adodb.connection")
> cn.connectstring = connectstring
> cn.Open
> Set GetConnection = cn
>
> end function
> %>
>
> In your case, you might add a parameter to this function to specify which
> connection to return.
> You can add a sub to close and destroy a connection as well.
>
> In my page, I do not call that function until immediately before I'm ready
> to use the connection. I take care of all the upfront work first (data
> validation, building sql statements, etc) then, if all is well, I call the
> function. That way, I'm not needlessly opening a connection if I don't
> need
> to.
>
> Also, since you are using Execute to run the SELECT statement, there is no
> need to instantiate a recordset object prior to running the Execute. In
> fact, it's a waste of time: the Execute statement creates its own
> recordset
> object behind the scenes. That recordset is returned, and, when a "set var
> =" statement was used to call the Execute statement, the recordset is
> assigned to var, replacing whatever var already contained, even if it
> contained nothing.
>
> Bob Barrows
> PS. Any progress on the actual problem in your OP?)
>
> Marc Llenas wrote:
>> Actually Bob, one more question about creation/destruction of conn &
>> rst.
>>
>> Because the application has several ASP pages, all of them working
>> with rst, I'm currently using an include that takes care of creating
>> the connection and the rst.. Based on your comments, I was wondering
>> if that is resource cost efficient or if I´d be better off manually
>> creating the conn and the rst every time I need them.
>>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Problem with Response.Request()

Marc Llenas wrote:
> Bob thanks for the tip on the include. Will implement that asap.
>
> About the original problem, still no luck. If I move the
> Response.Request to the beginning it works.
>
> -------------------------------------------------
>
> <%@ LANGUAGE = VBScript %>
>
>
>
> <%
>
> Response.redirect ("user_wish.asp")
>
> ------------------------------------------------
>
> Anywhere past the beginning it redirects to index.asp and there is no
> other redirect statements in the code... Very very strange.
>

Is there code in user_wish.asp that redirects to the other page if certain
conditions are met?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with Response.Request()

You nailed it.... user_wish.asp had a check that flagged a session variable
that would redirect to index.asp.

Now, another question (and sorry to keep pestering you). I tried
implementing the function and sub to create/destroy the conn as you
suggested.

When I run the code I get the following:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'objConn'
/vacances 2006/index.asp, línea 90

Calling page has the following code:





...
<%
Dim intUserDaysAvail
'----------------------------------------------------------- ---
Call GetConnection
strSQL = "SELECT TblDepartments.DepID, TblDepartments.DepName FROM
TblDepartments ORDER BY TblDepartments.DepName"
Set objRst = objConn.Execute(strSQL) '<<<<<<<<<< This is Line 90
'----------------------------------------------------------- ---
Do while objRst.eof = False
%>

Conn.inc has the following code:
<%@ LANGUAGE = VBScript %>
<% OPTION EXPLICIT %>
<%
Dim sPath
Dim connString
Dim strSQL

sPath = "C:\Inetpub\wwwroot\Vacances 2006\vacances.mdb"
connString = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & sPath &
";"

'Create new connection
Function GetConnection()
Dim objConn
Dim objRst

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = connString
objConn.Properties("Jet OLEDB:Database Password") = "xxxxxxxxx"
objConn.Open
Set GetConnection = objConn
End Function

'Destroy existing connection
Sub EndConnection(intConnNum)
objConn.Close
Set objConn = Nothing
End Sub
%>

Thanks,

Marc


"Bob Barrows [MVP]" escribió en el mensaje
news:OvJlvR$zFHA.2880@TK2MSFTNGP12.phx.gbl...
> Marc Llenas wrote:
>> Bob thanks for the tip on the include. Will implement that asap.
>>
>> About the original problem, still no luck. If I move the
>> Response.Request to the beginning it works.
>>
>> -------------------------------------------------
>>
>> <%@ LANGUAGE = VBScript %>
>>
>>
>>
>> <%
>>
>> Response.redirect ("user_wish.asp")
>>
>> ------------------------------------------------
>>
>> Anywhere past the beginning it redirects to index.asp and there is no
>> other redirect statements in the code... Very very strange.
>>
>
> Is there code in user_wish.asp that redirects to the other page if certain
> conditions are met?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Problem with Response.Request()

Marc Llenas wrote:
> When I run the code I get the following:
> Microsoft VBScript runtime (0x800A01F4)
> Variable is undefined: 'objConn'
> /vacances 2006/index.asp, línea 90

> Call GetConnection

GetConnection is a function, not a sub. You have to:

dim objConn
set objConn = GetConnection

Now you can use objConn

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with Response.Request()

All is good now.

Thanks a bunch Bob.

Marc

"Bob Barrows [MVP]" escribió en el mensaje
news:%23C7TUvA0FHA.464@TK2MSFTNGP15.phx.gbl...
> Marc Llenas wrote:
>> When I run the code I get the following:
>> Microsoft VBScript runtime (0x800A01F4)
>> Variable is undefined: 'objConn'
>> /vacances 2006/index.asp, línea 90
>
>> Call GetConnection
>
> GetConnection is a function, not a sub. You have to:
>
> dim objConn
> set objConn = GetConnection
>
> Now you can use objConn
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Problem with Response.Request()

Marc Llenas wrote:
>
> 'Destroy existing connection
> Sub EndConnection(intConnNum)
> objConn.Close
> Set objConn = Nothing
> End Sub
> %>

I just noticed this. This would be more effective:

Sub EndConnection (cn)
cn.close: set cn=nothing
End Sub


In your calling page, you would:

dim objConn
set objConn=getconnection
....
EndConnection objConn

I don't usually encapsulate this functionality: it's a single-line operation
so I'm not really gaining anything by creating an EndConnection sub. But
that's just me. I mentioned it in my previous post because I've seen other
people do it, so it seemed worthwhile to suggest it.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.