Help on error - Microsoft VBScript runtime error '800a01fb'

[Shadowspawn65]

I am having a problem with a stored proceedure that I am using in conjunction with ASP. I keep getting :

Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'CommandText'

Is there anyone out there that could let me know what this means and what I need to do to fix it.

ASP Code

StrConnect = "Provider=SQLOLEDB;Persist Security Info=False;" & _
				"User ID=*******;Initial Catalog=********; pwd=**************" 
				
%>
<!-- METADATA TYPE = "typeLib" File="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%	

Set dbConn = Server.CreateObject("ADODB.Connection")
Set dbComm = Server.CreateObject("ADODB.Command")		
dbConn.Open strConnect		
dbComm.ActiveConnection = dbConn
dbComm.CommandType = adCmdStoredProc

dbComm.CommandText = "sp_GetAddRegister"
				dbComm.Parameters.Append dbComm.CreateParameter("FirstName", adVarChar, adParamInput, 50, FirstName)
				dbComm.Parameters.Append dbComm.CreateParameter("LastName", adVarChar, adParamInput, 50, LastName)
				dbComm.Parameters.Append dbComm.CreateParameter("AuthorHandle", adVarChar, adParamInput, 50, AuthorHandle)
				dbComm.Parameters.Append dbComm.CreateParameter("Email", adVarChar, adParamInput, 100, Email)
				dbComm.Parameters.Append dbComm.CreateParameter("Detail", adVarChar, adParamInput, 300, Detail)
				dbComm.Parameters.Append dbComm.CreateParameter("Street", adVarChar, adParamInput, 50, Street)
				dbComm.Parameters.Append dbComm.CreateParameter("City", adVarChar, adParamInput, 50, City)
				dbComm.Parameters.Append dbComm.CreateParameter("State", adVarChar, adParamInput, 25, State)
				dbComm.Parameters.Append dbComm.CreateParameter("Zip", adVarChar, adParamInput, 10, Zip)
				dbComm.Parameters.Append dbComm.CreateParameter("Password", adVarChar, adParamInput, 50, Password)
				dbComm.Execute
				dbComm.Parameters.Refresh					
				'Signing user on
								
[COLOR=Red]				
				dbComm.CommandText = "sp_GetRegisterByAuthorHandle"								
[/color]				dbComm.Parameters.Append dbComm.CreateParameter("AuthorHandle", adVarChar, adParamInput, 50, AuthorHandle)
				
				Set rsRegister = dbComm.Execute
				Session("UserID") = rsRegister("UserID")
				Session("Alias") = rsRegister("AuthorHandle")
				rsRegister.Close
				Set rsRegister = Nothing

The first part is working fine. It is the stored proceedure sp_GetRegisterByAuthorHandle section that is crashing. Below is the code for the stored proceedure

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  PROCEDURE sp_GetRegisterByAuthorHandle
@AuthorHandle[VarChar](50)

AS 
SET NOCOUNT OFF 
SELECT *
FROM Rest_Register
WHERE AuthorHandle = @AuthorHandle


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[fattyfatpants]

A few things:

1) have you tried to execute the stored procedure from query analyzer to see if it works as expected??

2) why are you refreshing the parameters list? Because you are explicitly defining the parameters through dbcomm.parameters.append you don't need to use the dbcomm.parameters.refresh (this is grabbing the expected parameter list from the SP and allows you to reference the parameters collection by an index)

 

[Shadowspawn65]

fattyfatpants -


yes it is working in query analyzer -

second I have the refresh in there because the query above it is using more paramaters than this query (also included above) and if I don't use the refresh it gives me the error that states I have too many arguments for the procedure.

However I did get it to work by adding

Set dbConn = Server.CreateObject("ADODB.Connection")
					Set dbComm = Server.CreateObject("ADODB.Command")		
					dbConn.Open strConnect		
					dbComm.ActiveConnection = dbConn
					dbComm.CommandType = adCmdStoredProc

It seems by redoing the connection the error goes away.

Thanks for the help