problem invoking stored procedure

I'm trying to invoke a SQL server 7 stored procedure from asp 3.0 and I'm
having problems. The stored procedure accepts 2 input parameters and returns
4 output parameters. When I create the first parameter, I get the following
error when I try to append it to the command object:

Description: Parameter object is improperly defined. Inconsistent or
incomplete information was provided. I've checked everything I can think of,
including making sure the "adovbs.inc" file is included in the script. The
stored procedure and asp code follow:

STORED PROCEDURE CODE:
CREATE PROCEDURE GetPdf
@Program varchar(3),
@Application char(2),
@FileName varchar(100) = null OUT,
@DisplayCode varchar(8000) = null OUT,
@ValidationCode varchar(8000)= null OUT,
@ProcessingCode varchar(8000) = null OUT
AS

-- get PDF name & location. GV 6/28/06
set @FileName = (select Top 1 path from PDFLookup inner join PDFDocs on
PDFLookup.DocId = PDFDocs.DocId inner join PDFCode on PDFLookup.CodeId =
PDFCode.Codeid where brandedprogramcode = @Program and DocTypeId =
@Application)

-- get code used to display PDF with FDF content. GV 6/28/06
select @DisplayCode = (select cast(code as varchar(8000)) as code from
PDFLookup inner join PDFDocs on PDFLookup.DocId = PDFDocs.DocId inner join
PDFCode on PDFLookup.CodeId = PDFCode.Codeid where brandedprogramcode =
@Program and DocTypeId = @Application and CodeTypeId = 'FD')

-- get code used to validate FDF form entries. GV 6/28/06
select @ValidationCode = (select cast(code as varchar(8000)) as code from
PDFLookup inner join PDFDocs on PDFLookup.DocId = PDFDocs.DocId inner join
PDFCode on PDFLookup.CodeId = PDFCode.Codeid where brandedprogramcode =
@Program and DocTypeId = @Application and CodeTypeId = 'FV')

-- get code used to validate FDF form entries. GV 6/28/06
select @ProcessingCode = (select cast(code as varchar(8000)) as code from
PDFLookup inner join PDFDocs on PDFLookup.DocId = PDFDocs.DocId inner join
PDFCode on PDFLookup.CodeId = PDFCode.Codeid where brandedprogramcode =
@Program and DocTypeId = @Application and CodeTypeId = 'FP')


GO


ASP CODE:
set cn = Server.CreateObject("ADODB.Connection")
cn.open [connection string]
set objCmnd = Server.CreateObject("ADODB.Command")
objCmnd.ActiveConnection = cn
objCmnd.CommandText = "GetPdf"
objCmnd.CommandType = adCmdStoredProc

set objProgram = objCmnd.CreateParameter
set objApplication = objCmnd.CreateParameter
set objFileName = objCmnd.CreateParameter
set objDisplayCode = objCmnd.CreateParameter
set objValidationCode = objCmnd.CreateParameter
set objProcessingCode = objCmnd.CreateParameter

objProgram.Name = "Program"
objProgram.Type = adVarChar
objProgram.Direction = adParamInput
objProgram.Value = Session("BrandedProgramCode")

'-- this is the line that causes the error
objCmnd.Parameters.Append objProgram

objApplication.Name = "Application"
objApplication.Type = adChar
objApplication.Direction = adParamInput
objProgram.Value = "CF"
objCmnd.Parameters.Append objApplication

objFileName.Name = "FileName"
objFileName.Type = adVarChar
objFileName.Direction = adParamOutPut
objCmnd.Parameters.Append objFileName

objDisplayCode.Name = "DisplayCode"
objDisplayCode.Type = adLongVarchar
objDisplayCode.Direction = adParamOutPut
objCmnd.Parameters.Append objDisplayCode

objValidationCode.Name = "ValidationCode"
objValidationCode.Type = adLongVarchar
objValidationCode.Direction = adParamOutPut
objCmnd.Parameters.Append objValidationCode

objProcessingCode.Name = "ProcessingCode"
objProcessingCode.Type = adLongVarchar
objProcessingCode.Direction = adParamOutPut
objCmnd.Parameters.Append objProcessingCode


objCmnd.Execute
strFdFContents = objCmnd.parameters("DisplayCode").value

.

Relevant Pages

  • Re: procedure tuning
    ... Pro SQL Server 2000 Database Design - ... >>> INNER JOIN tblVRM ... >>> INNER JOIN tblFDVSourceSurvey ... I am having problems re-writting a stored procedure to run quicker, ...
    (microsoft.public.sqlserver.programming)
  • RE: Strange bug, hard to reproduce - is it known?
    ... > tables used in the query, nor the query itself were changed (I did make ... > particular stored procedure), yet it did work correctly before. ... > tables involved in my test database, using the EXACT same columns, ... > INNER JOIN dbo.GR AS r ...
    (microsoft.public.sqlserver.programming)
  • Strange bug, hard to reproduce - is it known?
    ... tables used in the query, nor the query itself were changed (I did make ... particular stored procedure), yet it did work correctly before. ... tables involved in my test database, using the EXACT same columns, ... INNER JOIN dbo.GR AS r ...
    (microsoft.public.sqlserver.programming)
  • Re: unable to define transformations with a dts using a stored procedure (SQL Server 2000)
    ... the stored proc) ... at the transformations. ... from a stored procedure and output it to the file. ... INNER JOIN CLARITY_EAP as EAP ...
    (microsoft.public.sqlserver.dts)
  • RE: String as output from SP in C++
    ... actually had 2 queries in it and the Parameters collection was not cleared ... Thanks Mike - LOL ... string from a stored procedure. ... adParamOutput, 2560, var_phy_email); ...
    (microsoft.public.data.ado)