What's wrong in Classic ASP StoredProcedure insert statement

Question

Here is the classic ASP code

Set objCommandSec = CreateObject("ADODB.Command")
        With objCommandSec
            Set .ActiveConnection = MyConn
            .CommandType = adCmdStoredProc
            .CommandText = "ReportsPDFInsert"

            .CreateParameter "@StatsID", adInteger, adParamInput
            .Parameters("@StatsID") = xStats_ID

            .CreateParameter "@MemberID", adInteger, adParamInput
            .Parameters("@MemberID") = xMemberID

            .CreateParameter "@LanguageID", adInteger, adParamInput
            .Parameters("@LanguageID") = 1  '1=EN

            .CreateParameter "@PDFFilename", adVarWChar , adParamInput
            .Parameters("@PDFFilename") = PDFFilename

            .Execute
        End With

Here is the stored procedure code

ALTER PROCEDURE [dbo].[ReportsPDFInsert]
-- Add the parameters for the stored procedure here
@StatsID INT
,@MemberID INT
,@LanguageID     INT
,@PDFFilename NVARCHAR(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[ReportsPDF]
       ([StatsID]
       ,MemberID
       ,[LanguageID]
       ,[PDFFilename]
       ,[DateCreated])
 VALUES
       (@StatsID
       ,@MemberID
       ,@LanguageID
       ,@PDFFilename
       ,GETDATE())

END

I get error as

Error number: -2147217904

Error description: Procedure 'ReportsPDFInsert' expects parameter '@StatsID', which was not supplied.

Source: Microsoft OLE DB Provider for SQL Server

If I execute the stored procedure itself, then it is working fine. I have similar classic asp code in other page, and that works fine as well. yes, I made sure xStats_ID does have value. I printed just before the .Execute and I see the value.

Please somebody shed some light. Thanks

Is `MyConn` a connection string or a connection object ? try `.ActiveConnection = MyConn` — Flakes, May 26 '13 at 07:11

score 2 · Answer 1 · edited May 23 '17 at 10:25

2

Try appending the parameters explicitly using something like this:

cmd.Parameters.Append cmd.CreateParameter("@StatsID",adInteger, adParamInput,xStats_ID)

instead of .Parameters("")

Here is another post that might help: How to make a parametrized SQL Query on Classic ASP?

edited May 23 '17 at 10:25

Community

1
1

answered May 24 '13 at 17:49

Ashraf ElSwify

192
1
8

Nope, the same error msg I even tried with 0 for size parameter – Nathan May 24 '13 at 18:13
Although this didn't solve the specific issue the OP had, it's good advice. It's important to note that [`CreateParameter`](http://msdn.microsoft.com/en-us/library/windows/desktop/ms677209%28v=vs.85%29.aspx) by itself only creates a `Parameter` object, _without_ appending it to the `Parameters` collection. The reason the original code works is that ADO is querying the database to automatically fill the collection (see [docs](http://msdn.microsoft.com/en-us/library/windows/desktop/ms677209%28v=vs.85%29.aspx)). The `CreateParameter` calls in the OP are unnecessary. – Cheran Shunmugavel Jul 09 '13 at 04:37

score 1 · Accepted Answer · answered Jul 08 '13 at 20:54

1

Only today I figured what was the problem.

I haven't included the adovbs.inc file for the constants to work.

And I don't know why it was throwing some other error message.

good reason to move away from Classic ASP [only if my boss listens]

answered Jul 08 '13 at 20:54

Nathan

137
3
14

score 0 · Answer 3 · answered May 24 '13 at 22:29

0

Try dropping the "@" in your parameter names.

answered May 24 '13 at 22:29

William Jens

422
2
6
15

What's wrong in Classic ASP StoredProcedure insert statement

3 Answers3