variable in ASP page to stored procedure sql 7

[lloydanderson]

I would appreciate any help with the following code - I am trying to pass a variable from one ASP page to another and then use that variable in a stored procedure. Some of the code follows.

Dim vHotelname
vHotelname=Request.QueryString("Hotel Name&quot
Dim adCmdStoredProc
adCmdStoredProc = 4

Set objCmd = Server.CreateObject("ADODB.Command&quot
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "pickhotel"
objCmd.Parameters.Append.cmd.CreateParameter = ("HotelName", adVarChar, adParamInput, 100, vhotelname)
objCmd.CommandType = adCmdStoredProc
Set objRS = Server.CreateObject("ADODB.Recordset&quot
objRS.Execute objCmd



Alter Procedure pickhotel ( @HotelName varchar(100) )
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As
/* set nocount on */
EXEC('SELECT [Hotels Table].[Hotel Name], Actuals.[Hotel ID], Actuals.[Date ID], Actuals.[Month ID].......
WHERE Actuals.[Hotel ID] LIKE ''%' + @HotelName + '%'' AND .... )

Thanks in advance

 

[SrinuReddy]

Set objCmd = Server.CreateObject("ADODB.Command&quot
Set objRS = Server.CreateObject("ADODB.Recordset&quot

with objCmd
.ActiveConnection = objConn
.CommandType = adCmdStoredProc
.CommandText = "pickhotel"
.Parameters.Append .CreateParameter("HotelName", adVarChar, adParamInput, 100, vhotelname)
end with
objRS.Open cmd


The above is a part of the code which I modified ... check out that there is a space after "append" .. 2 statements combined together ...

set recordset locktype and cursur type as required ...

hope this helps ..
cheers!!!
srinu....

 

[lloydanderson]

I am still having problems - I tried changing the page:


Dim strSQL
Dim objConn
Dim objComm
Dim objRS
Dim objField
Dim strTextWrite
Dim lngRSCount
Dim vHotelname
vHotelname=Request.QueryString("Hotel Name&quot
'vHotelname = "PHX"

Dim adCmdStoredProc
adCmdStoredProc = 4


Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open "DSN=Mrkseg"
Set objCmd = Server.CreateObject("ADODB.Command&quot
Set objCmd.ActiveConnection = objConn
strSQL = "pickhotel'" & vHotelname & "'"
'strSQL = "pickhotel"
'objCmd.CommandText = "pickhotel"
objCmd.CommandType = adCmdStoredProc

Set objRS = objConn.execute(strSQL)

What happens is:
With- vHotelname=Request.QueryString("Hotel Name&quot
the sp seems to accept a null value. I think this because I receive all records.

If I change to vHotelname = "PHX" - The record returns correctly.

Again, I am trying to pass a variable from one asp to another. The first page goes to the MSSQL and retrieves the "Hotel Name" and passes it to the second page. I am trying to send the variable back to a MSSQL sp as a parameter.

Thank you for your help...

 

[JuanitaC]

Right after you request your value from the previous page, write it to the screen so you can verify that it is there...


vHotelname=Request.QueryString("Hotel Name&quot
Response.write(&quot;Hotel is &quot; & vHotelname & &quot;<br>&quot

And I just noticed that you are requesting &quot;Hotel Name&quot; from your query string... try to change that to HotelName with no space, on your sending page as well as your receiving page... query strings don't carry spaces well (if at all). That might fix up your problem.

 

[lloydanderson]

Dooh!

I was not returning anything in the Querystring

I was reusing a asp page and did not even think about it -

Thank you very much for your help...