(0x80040E10) Error

Trying to use the command object with Parameters in ASP:

The stored procedure should insert a record and return with a id(unique identifier created by SQL db).

This works well from Query Analizer, but I keep getting the following error in my asp page:
Error Type:
(0x80040E10)

the first parameter is my return value, all other values are nvarchar, int and datetime.

I tried everything but got the same error again and again.


Can you help me?

Thanks in advance!
Mollie

------------------------------------------------------------ -----------------------------------------------
Here are the codes:

1. Stored procedure:

CREATE PROCEDURE dbo.sp_AddSession
(
@ip varchar(50),
@user_id int,
@login_time datetime,
@update_time datetime,
@s_key varchar(50) OUTPUT
)
AS
BEGIN
declare @session uniqueidentifier
set @session = newid()
INSERT INTO dbo.Session (s_key,ip_addr,user_id,login_time,update_time)
VALUES (@session, @ip, @user_id, @login_time, @update_time)

select @s_key = cast(@session as varchar(50))

END

------------------------------------------------------------ --------------------------------------------------
2. ASP code:

Dim session_key
Dim objCmd, objConn, connString
set objCmd = Server.CreateObject("ADODB.Command")
Set objConn = Server.CreateObject("ADODB.Connection")

connString = "..."
objConn.Open connString

With objCmd
.ActiveConnection = objConn
.CommandText = "{?=call sp_AddSession(?)}"
.CommandType = adCmdStoredProc
'add output parameter
.Parameters.Append .CreateParameter("@s_key", adVarWChar, adParamReturnValue, 50)
'add input parameters
.Parameters.Append .CreateParameter("@ip", adVarWChar, adParamInput, 50)
.Parameters.Append .CreateParameter("@user_id", adInteger, adParamInput)
.Parameters.Append .CreateParameter("@Login_time", adDBDate, adParamInput)
.Parameters.Append .CreateParameter("@Update_time", adDBDate, adParamInput)
'set the parameters value
.Parameters("@ip") = Request.ServerVariables("REMOTE_ADDR")
.Parameters("@user_id") = Session("userid")
.Parameters("@Login_time") = now()
.Parameters("@Update_time") = now()
'run the stored procedure
.Execute, , adExecuteNoRecords
'extract the return value
session_key = .Parameters("@s_key")
End With

objCom.close
objConn.close
Set objCmd = Nothing
Set objConn = Nothing
------------------------------------------------------------ -----------------------------------------------

From http://developmentnow.com/g/61_2005_6_0_0_0/inetserver-asp-d b.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Re: (0x80040E10) Error

"Mollie" wrote in message
news:16161d57-d8cb-4414-aae8-a580eb68c3d8@msnews.microsoft.c om...
> Trying to use the command object with Parameters in ASP:
>
> The stored procedure should insert a record and return with a id(unique
> identifier created by SQL db).
>
> This works well from Query Analizer, but I keep getting the following
> error in my asp page:
> Error Type:
> (0x80040E10)
>
> the first parameter is my return value, all other values are nvarchar, int
> and datetime.
>
> I tried everything but got the same error again and again.

1. Why do you only have one ? placeholder when sp_AddSession accepts 5
parameters?
2. Return values can only be integers, and are returned using the RETURN
statement in the stored procedure.
3. You should not name your stored procedures starting with "sp_" unless you
create them in master and write them to work from any database context
(because doing so incurs the overhead of a needless context switch.)

To make it work, change your command text string, append @s_key as the last
parameter, and make it's direction adParamOutput.

-Mark







> Can you help me?
>
> Thanks in advance!
> Mollie
>
> ------------------------------------------------------------ -----------------------------------------------
> Here are the codes:
>
> 1. Stored procedure:
>
> CREATE PROCEDURE dbo.sp_AddSession
> (
> @ip varchar(50),
> @user_id int,
> @login_time datetime,
> @update_time datetime,
> @s_key varchar(50) OUTPUT
> )
> AS
> BEGIN
> declare @session uniqueidentifier
> set @session = newid()
> INSERT INTO dbo.Session (s_key,ip_addr,user_id,login_time,update_time)
> VALUES (@session, @ip, @user_id, @login_time, @update_time)
>
> select @s_key = cast(@session as varchar(50))
>
> END
>
> ------------------------------------------------------------ --------------------------------------------------
> 2. ASP code:
>
> Dim session_key
> Dim objCmd, objConn, connString
> set objCmd = Server.CreateObject("ADODB.Command")
> Set objConn = Server.CreateObject("ADODB.Connection")
>
> connString = "..."
> objConn.Open connString
>
> With objCmd
> .ActiveConnection = objConn
> .CommandText = "{?=call sp_AddSession(?)}"
> .CommandType = adCmdStoredProc
> 'add output parameter
> .Parameters.Append .CreateParameter("@s_key", adVarWChar,
> adParamReturnValue, 50)
> 'add input parameters
> .Parameters.Append .CreateParameter("@ip", adVarWChar, adParamInput, 50)
> .Parameters.Append .CreateParameter("@user_id", adInteger, adParamInput)
> .Parameters.Append .CreateParameter("@Login_time", adDBDate,
> adParamInput)
> .Parameters.Append .CreateParameter("@Update_time", adDBDate,
> adParamInput)
> 'set the parameters value
> .Parameters("@ip") = Request.ServerVariables("REMOTE_ADDR")
> .Parameters("@user_id") = Session("userid")
> .Parameters("@Login_time") = now()
> .Parameters("@Update_time") = now()
> 'run the stored procedure
> .Execute, , adExecuteNoRecords
> 'extract the return value
> session_key = .Parameters("@s_key")
> End With
>
> objCom.close
> objConn.close
> Set objCmd = Nothing
> Set objConn = Nothing
> ------------------------------------------------------------ -----------------------------------------------
>
> From http://developmentnow.com/g/61_2005_6_0_0_0/inetserver-asp-d b.htm
>
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Re: (0x80040E10) Error

Mollie wrote:
> Trying to use the command object with Parameters in ASP:
>
> The stored procedure should insert a record and return with a
> id(unique identifier created by SQL db).
>
You may be interested in my stored procedure code generator available (for
free) here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear

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: (0x80040E10) Error

Hi Mark,

Thanks so much for your help! I did get expected result after:
1. Instead using formal way of calling a stored procedure, I change to the
simplest way: .CommandText = "sp_AddSession"
2. I changed the parameterType for output to 'adParamOutput', because it's
not the interger. I didn't realise this before.

And appreciated for your suggestion of avoid naming of "sp_" for stored
procedures. It's never too late to know this!!

Best regards,
Mollie



"Mark J. McGinty" wrote:

>
> 1. Why do you only have one ? placeholder when sp_AddSession accepts 5
> parameters?
> 2. Return values can only be integers, and are returned using the RETURN
> statement in the stored procedure.
> 3. You should not name your stored procedures starting with "sp_" unless you
> create them in master and write them to work from any database context
> (because doing so incurs the overhead of a needless context switch.)
>
> To make it work, change your command text string, append @s_key as the last
> parameter, and make it's direction adParamOutput.
>
> -Mark