Stored Procedure Not Working For Some Reason

Results 1 to 2 of 2

Thread: Stored Procedure Not Working For Some Reason

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedure Not Working For Some Reason

    I have the following stored procedure on a SQL Server 7 box:<BR><BR>-----------------------------<BR><BR>PROCEDURE sp_NewSession<BR> @USRIPADDR VARCHAR(50)<BR>AS<BR> DECLARE @SESSIONID UNIQUEIDENTIFIER<BR> SET @SESSIONID = NEWID()<BR> DECLARE @DT DATETIME<BR> SET @DT = GETDATE()<BR> INSERT INTO tblSessions<BR> (txtSessionID, timStamp, txtIPAddress)<BR> VALUES<BR> (@SESSIONID, @DT, @USRIPADDR)<BR> SELECT txtSessionID FROM tblSessions WHERE txtSessionID = @SESSIONID<BR><BR>-----------------------------<BR>Next I have the following VBScript procedure that calls that stored procedure:<BR><BR>-----------------------------<BR><BR>Function NewSession() <BR> Dim oConn, oRs, usripaddr, oSQL<BR> Set oConn = Server.CreateObject("ADODB.Connection")<BR> oConn.Open strGConn<BR> usripaddr = Request.ServerVariables("REMOTE_ADDR")<BR> oSQL = "EXECUTE sp_NewSession &#039" & usripaddr & "&#039"<BR> Set oRs = oConn.Execute(oSQL)<BR> NewSession = oRs("txtSessionID")<BR> oRs.Close<BR> Set oConn = Nothing<BR>End Function<BR><BR>--------------------------<BR>When I call the NewSession procedure from an ASP page, I get the following error in my browser:<BR><BR>ADODB.Recordset error &#039 800a0cc1&#039 <BR><BR>Item cannot be found in the collection corresponding to the requested name or ordinal. <BR><BR>/tcnew/inc/conn.asp, line 17 <BR><BR>The error refers to the line:<BR>NewSession = oRs("txtSessionID")<BR><BR>I know that the table has that field in it and the INSERT function in the stored procedure is working, but why am I getting this error???<BR><BR>Thanks for your anticipated help.<BR>-- Rob Dugre --<BR><BR>

  2. #2
    pillai Guest

    Default RE: Stored Procedure Not Working For Some Reason

    when u use stored procedure use the command object.<BR>set cmd=server.CreateObject ("adodb.command")<BR> <BR> cmd.ActiveConnection="provider=sqloledb;server=nt_ server;database=master;uid=sa;pwd=;"<BR> cmd.CommandText="storedprocedurename" <BR> cmd.CommandType=4<BR> cmd.CreateParameter "@name",200,,255<BR> cmd("@name")=trim(Request.Form("text1"))<BR> cmd.CreateParameter "@pass",200,,255<BR> cmd("@pass")=trim(Request.Form("password1"))<BR> cmd.Execute<BR> Response.Write "stored proc executed"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts