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

Re: problem invoking stored procedure

Glenn Venzke wrote:
> 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:
>

You will probably appreciate this command object code generator:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip

Using your create procedure statement, the code that my tool generates
is shown below:


> 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
>

None of this is relevant. The only part needed to decide what parameters
to create in vbscript/ado is the above.

Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "GetPdf"
set .ActiveConnection=cnSQL
set param = .createparameter("RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@Program", adVarChar, adParamInput, 3,
[put value here])
.parameters.append param
set param = .createparameter("@Application", adVarChar, adParamInput,
2, [put value here])
.parameters.append param
set param = .createparameter("@FileName", adVarChar,
adParamInputOutput, 100, [put value here])
.parameters.append param
set param = .createparameter("@DisplayCode", adVarChar,
adParamInputOutput, 8000, [put value here])
.parameters.append param
set param = .createparameter("@ValidationCode", adVarChar,
adParamInputOutput, 8000, [put value here])
.parameters.append param
set param = .createparameter("@ProcessingCode", adVarChar,
adParamInputOutput, 8000, [put value here])
.parameters.append param
.execute ,,adexecutenorecords
end with

If you do not wish to supply values for your output parameters, replace
adParamInputOutput with adParamOutput


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: problem invoking stored procedure

Bob Barrows [MVP] wrote:
> set param = .createparameter("RETURN_VALUE", adInteger,
> adParamReturnValue, 0)

Oh! Watch the line wrap ... the above (as well as all the following
statements) should be a single line.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: problem invoking stored procedure

works great. Thanks!! Just one question, what is the 4th argument in the
"CreateParameter" statement for? What do those integers represent? That
argument is omitted in my Wrox books.

"Bob Barrows [MVP]" wrote:

> Glenn Venzke wrote:
> > 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:
> >
>
> You will probably appreciate this command object code generator:
> http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
>
> Using your create procedure statement, the code that my tool generates
> is shown below:
>
>
> > 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
> >
>
> None of this is relevant. The only part needed to decide what parameters
> to create in vbscript/ado is the above.
>
> Dim cmd, param
>
> Set cmd=server.CreateObject("ADODB.Command")
> With cmd
> .CommandType=adcmdstoredproc
> .CommandText = "GetPdf"
> set .ActiveConnection=cnSQL
> set param = .createparameter("RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> .parameters.append param
> set param = .createparameter("@Program", adVarChar, adParamInput, 3,
> [put value here])
> .parameters.append param
> set param = .createparameter("@Application", adVarChar, adParamInput,
> 2, [put value here])
> .parameters.append param
> set param = .createparameter("@FileName", adVarChar,
> adParamInputOutput, 100, [put value here])
> .parameters.append param
> set param = .createparameter("@DisplayCode", adVarChar,
> adParamInputOutput, 8000, [put value here])
> .parameters.append param
> set param = .createparameter("@ValidationCode", adVarChar,
> adParamInputOutput, 8000, [put value here])
> .parameters.append param
> set param = .createparameter("@ProcessingCode", adVarChar,
> adParamInputOutput, 8000, [put value here])
> .parameters.append param
> .execute ,,adexecutenorecords
> end with
>
> If you do not wish to supply values for your output parameters, replace
> adParamInputOutput with adParamOutput
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: problem invoking stored procedure

never mind. I figured it out. It's the size of the variables used for the
parameters

"Glenn Venzke" wrote:

> works great. Thanks!! Just one question, what is the 4th argument in the
> "CreateParameter" statement for? What do those integers represent? That
> argument is omitted in my Wrox books.
>
> "Bob Barrows [MVP]" wrote:
>
> > Glenn Venzke wrote:
> > > 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:
> > >
> >
> > You will probably appreciate this command object code generator:
> > http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
> >
> > Using your create procedure statement, the code that my tool generates
> > is shown below:
> >
> >
> > > 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
> > >
> >
> > None of this is relevant. The only part needed to decide what parameters
> > to create in vbscript/ado is the above.
> >
> > Dim cmd, param
> >
> > Set cmd=server.CreateObject("ADODB.Command")
> > With cmd
> > .CommandType=adcmdstoredproc
> > .CommandText = "GetPdf"
> > set .ActiveConnection=cnSQL
> > set param = .createparameter("RETURN_VALUE", adInteger,
> > adParamReturnValue, 0)
> > .parameters.append param
> > set param = .createparameter("@Program", adVarChar, adParamInput, 3,
> > [put value here])
> > .parameters.append param
> > set param = .createparameter("@Application", adVarChar, adParamInput,
> > 2, [put value here])
> > .parameters.append param
> > set param = .createparameter("@FileName", adVarChar,
> > adParamInputOutput, 100, [put value here])
> > .parameters.append param
> > set param = .createparameter("@DisplayCode", adVarChar,
> > adParamInputOutput, 8000, [put value here])
> > .parameters.append param
> > set param = .createparameter("@ValidationCode", adVarChar,
> > adParamInputOutput, 8000, [put value here])
> > .parameters.append param
> > set param = .createparameter("@ProcessingCode", adVarChar,
> > adParamInputOutput, 8000, [put value here])
> > .parameters.append param
> > .execute ,,adexecutenorecords
> > end with
> >
> > If you do not wish to supply values for your output parameters, replace
> > adParamInputOutput with adParamOutput
> >
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >
> >