CODEWITHSUNDEEP
stored-proceduresasp-classic
Nathan
Nathan

Reputation: 170

What's wrong in Classic ASP StoredProcedure insert statement

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

Upvotes: 0

Views: 572

Answers (3)

Nathan
Nathan

Reputation: 170

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]

Upvotes: 1

William Jens
William Jens

Reputation: 442

Try dropping the "@" in your parameter names.

Upvotes: 0

Ashraf ElSwify
Ashraf ElSwify

Reputation: 192

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?

Upvotes: 2

Related Questions