Classic asp insert using SQL Server 2008 stored proc says I either have too many parameters, or the parameter was not specified

Question

It's been a long time since I did any classic asp work, but was recently asked to pick up a project that someone else had been working on which was a mess.

The site has a SQL Server 2008 database at the back end and I need to add a record and retrieve the auto identity column as an out parameter.

My asp code is shown here...

Call fncOpenData()
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = oConn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "usp_ins_MyRecord"
cmd.Parameters.Refresh
cmd.Parameters.Append cmd.CreateParameter("@MyValue", adVarChar, adParamInput, 10)
cmd.Parameters("@MyValue") = Request.Form("MyValue")
cmd.Execute

and my stored procedure was created with the code here...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE usp_ins_MyRecord
    @MyValue    varchar(10)
AS
BEGIN
    INSERT INTO [MyRecords]
    (
        [MyValue]
    )
    VALUES
    (
        @MyValue
    )
END
GO

Now, when I run my asp page I get the following error...

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function usp_ins_MyRecord has too many arguments specified.

/MyPage.asp, line ..."

If I remove the line

cmd.Parameters.Refresh

I get the following error...

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'usp_ins_MyRecord' expects parameter '@MyValue', which was not supplied.

/MyPage.asp, line ..."

Erm, yes it was?!

If I change my code in any other way to add the parameter, such as

cmd.Parameters.Append cmd.CreateParameter("@MyValue", adVarChar, adParamInput, 10, Request.Form("MyValue")

or

myValue = Request.Form("MyValue")
Set param = cmd.CreateParameter("@MyValue", advarchar, adParamInput, 10)
cmd.Parameters.Append param
param.Value = myValue

I get the same error as before, stating the parameter wasn't specified?

I know the technology is dated and really wish I could use .Net but this is what I have to use, but I'm pulling my hair out here and getting nowhere fast.

Can anyone see what's wrong, or explain why I'm having trouble getting ASP to use SQL Server 2008 stored procs?

Thanks.

Have you tried putting `cmd.CommandType = adCmdStoredProc` after `cmd.CommandText = "usp_ins_MyRecord"`? — Andriy M, Jan 14 '12 at 22:57

Jason · Accepted Answer · 2012-01-14T16:31:01.977

The Refresh method from the ADODB Parameters collection automatically adds the parameters from the stored procedure to your command.

Either use Refresh or add the parameters separately using CreateParameter.

If the provider will allow, you can populate the Parameters Collection by using the Refresh method. In fact, if you try to access this collection while it is empty or before you have called Refresh for the first time, ADO will automatically call Refresh to populate the collection. It is more efficient to provide the parameters, rather than having to call and obtain this information from the provider. (Anything you can do to reduce calls to the provider will improve performance.) You can add Parameter objects using the Append property.

Ref: http://www.devguru.com/technologies/ado/quickref/command_parameterscollection.html

UPDATE

You should could also try to remove the @ sign when you create your parameter.

cmd.Parameters.Append cmd.CreateParameter("MyValue", adVarChar, adParamInput, 10, Request.Form("MyValue"))

score 1 · Answer 2 · answered Nov 20 '12 at 16:01

I was in an identical situation in almost every way...including why I was working with Classic ASP and the flipping between error messages. The accepted response was helpful in deciding not to use "cmd.Parameters.Refresh"

OK so why was I getting the "expects parameter" error when it clearly was there? In my case...the line where I was creating the parameter was missing a part for the CreateParameter call:

I had:

cmd.Parameters.Append cmd.CreateParameter("@PoNum", 3, 1, PONum) 'adInteger = 3, adInputParam = 1

Incorrectly I was thinking I didn't need to specify a size for an integer. Size zero seems to work though... So things began to work correctly when I put zero in for the size.

cmd.Parameters.Append cmd.CreateParameter("@PoNum", 3, 1, 0,PONum) 'adInteger = 3, adInputParam = 1

Sorry I wasn't using the adxxxxx constants...I inherited some code here and I couldn't easily convert to using those constants...because all through the code, some of those necessary constants where used as variables. I can't wait to get back to DotNet.

score 0 · Answer 3 · answered Jan 17 '12 at 14:39

Try a simpler way of executing stored procedure within asp classic code using ADODB.Connection rather than ADODB.Command...

Set con = Server.CreateObject( "ADODB.Connection" )
con.Open oConn

sql = "exec usp_ins_MyRecord @myValue='" & somevalue & "'"
Set RS = con.execute(sql)

Then add a line into stored procedure to get the identity back of recently added record

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE usp_ins_MyRecord
    @MyValue    varchar(10)
AS
BEGIN
    INSERT INTO [MyRecords]
    (
        [MyValue]
    )
    VALUES
    (
        @MyValue
    )

SELECT @@identity AS recordid
END
GO

Then you can pull that back from recordset

IF NOT RS.EOF THEN
  myRecordid = RS("recordid")
END IF

...except this is vulnerable to [SQL injection](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain) — Cheran Shunmugavel, Jan 17 '12 at 16:28
I agree but can be protected by sanitizing input, using correct data types, escaping apostrophes, etc and protecting querystring input at a server level using tools like URLScan — Andy Davies, Jan 17 '12 at 20:30

Classic asp insert using SQL Server 2008 stored proc says I either have too many parameters, or the parameter was not specified

3 Answers3