ASP parameters to call a stored procedure in SQL S

Question

I try call this stored procedure in SQL Server that inserts data.

I have this:

CREATE PROCEDURE [dbo].[AddUser]
   @user_id bigint,
   @user_name varchar(20)
AS
BEGIN
   INSERT INTO [users] ([id], [name]) 
   VALUES (@user_id, @user_name)
END

and I have this ASP code:

<%
Const adCmdStoredProc = 4
Const adBigInt = 20
Const adParamInput  = 1
Const adVarChar     = 200

Set conn = Server.CreateObject("ADODB.Connection")
connectionstring = "Provider=SQLOLEDB.1;Password=***********;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=AREAWEB2-PC"
conn.Open connectionstring
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "{call AddUser (?,?)}"

frm_id = 1
frm_name = "Carlos"

Set prmUserId = cmd.CreateParameter("@user_id", adBigInt, adParamInput, 0, frm_id)
Set prmUserName = cmd.CreateParameter("@user_name", adVarChar, adParamInput, 20, frm_name)

Cmd.Parameters.Append prmUserId
Cmd.Parameters.Append prmUserName

Set rs = cmd.Execute 


%>

But I get this error message:

Microsoft OLE DB Provider for SQL Server error '80040e10'

What is wrong ?

Answer 1

If you Google the error code 80040e10 it becomes apparent quickly that the issue is one of the following to do with your parameter definitions;

From Why do I get 80040E10 errors?

Microsoft OLE DB Provider for SQL Server error '80040e10' 
Procedure '' expects parameter '', which was not supplied.

Your issue is likely due to passing a 0 size in your @user_id parameter, as all parameters have to pass a maximum size either using .CreateParameter() or before appending a parameter to the collection using .Size property.

---

Update:

As the OP has pointed out the issue is actually due to the wrong CommandType property value being set.

Thought it would be worth expanding the answer to explain why. The reason is the CommandType in the OP's example is set to adCmdStoredProc but the .CommandText property is not set to a Stored Procedure name, the easiest way to correct this (instead of removing the .CommandType) is to change the .CommandText as follows;

cmd.CommandText = "AddUser"

The other option is to change the .CommandType to adCmdText which will accept the format {call AddUser (?,?)}.

cmd.CommandType = adCmdText

Internally ADO uses adCmdText for the adCmdStoredProc CommandTypeEnum as the .CommmandText will get changed to the format {call AddUser (?,?)}, the difference is you cannot use this form yourself unless you specify .CommandType = adCmdText. There is no additional benefits to using either one, but in my opinion adCmdStoredProc just simplifies the calls and hides the nuts and bolts from you.

Answer 2

The code fails because I have these incorrect:

cmd.CommandType = adCmdStoredProc

Removing it, works.