February 15, 2007 at 2:19 pm
CREATE PROCEDURE dbo.zsp_PgValsKey
@Uservarchar(50),
@ProcFlagchar(1)=null,
AS
SET NOCOUNT ON
DECLARE @sqlvarchar(8000)
IF (@ProcFlag = 'L') /* return list of values from various tables */
BEGIN
select distinct
a.sessionnum,
a.pagestate,
case a.pageState WHEN 'GRD' THEN c.iprt_grd WHEN 'SZ' THEN c.iprt_size ELSE a.pgVal END AS pgVal,
b.pgStateOrder
from pgValsKey as a
inner join
PageState as b
on a.PageState = b.pgState
left join iprtprd as c
on (CAST(a.pgVal AS VARCHAR) = CAST(c.chkGrpGrd AS VARCHAR) and a.pagestate = 'GRD' ) OR ( CAST(a.pgVal AS VARCHAR) = CAST(c.chkGSG AS VARCHAR) and a.pagestate = 'SZ')
where a.User =@User
order by pgStateOrder
END
-----------------------------------
In the asp code, I did the following:
Dim cmdPageState
Set cmdPageState= Server.CreateObject("ADODB.Command")
With cmdPageState
.ActiveConnection = cnStr
.CommandText = "zsp_pgValsKey"
.CommandType = adCmdStoredProc
' create parameters
.Parameters.Append .CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue)
.Parameters.Append .CreateParameter("@User",adVarChar,adParamInput,50,IPortUserLogin)
.Parameters.Append .CreateParameter("@ProcFlg",adChar,adParamInput,1,pgName)
Session("strErr") = cmdPageState.CommandText & "
" & IPortUserLogin & "
" & pgName & "
"
End With
set rs = cmdPageState.Execute()
if not(rs.EOF) then
'do stuff
else
' cannot do stuff - no recordset
end if
when I run this code from QA - the procedure returns information with the input values.
when I run the code, I receive the following error message:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/iPortforms/Stock/StockStatSegments/A1.asp, line
So the problem is no recordset is being passed back. If I comment out the check for the rs.EOF and the code beneath IF statement, the code works.
What is strange, these problems just began last week on our web server. As far as I know there were no changes to the web server. I've been working on this problem for 2 days. What's wierd is I had another procedure last week that did this, but that was resolved with the SET NOCOUNT ON statement in the procedure which as you can see is in this procedure.
I'm truly flabbergasted with this as we use similiar coding in other procedures and web pages and all of sudden we're starting to see these problems.
February 15, 2007 at 7:04 pm
It seems like you have two issues here.
1. Microsoft VBScript runtime error '800a01a8'
Object required: ''
/iPortforms/Stock/StockStatSegments/A1.asp, line
I am not too certain whether you figured out this issue??? I do not have any idea for this.
2. rs.EOF issue which did not return the result set.
For #2, I suggest you add "ActiveConnection.CursorLocation = 3" as below:
With cmdPageState
.ActiveConnection = cnStr
.ActiveConnection.CursorLocation = 3 'adUseClient
.CommandText = "zsp_pgValsKey"
.CommandType = adCmdStoredProc
' create parameters
.Parameters.Append .CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue)
.Parameters.Append .CreateParameter("@User",adVarChar,adParamInput,50,IPortUserLogin)
.Parameters.Append .CreateParameter("@ProcFlg",adChar,adParamInput,1,pgName)
...
End With
This cursorlocation configures whether the resultset returns to server or client. See how it goes...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply