Calling stored procedure on connection

[dw]

Hello all, I'm having a dickens of a time calling a stored procedure on a
connection. Every time I do, it generates an error "Arguments are of the
wrong type, are out of acceptable range, or are in conflict with one
another." I've run the same stored procedure with the same exact list of
arguments in Query Analyser in SQL Server, and it works. The page has the
adovbs constants. Note that uspGetProperties is a stored procedure in SQL
Server.

Set rsProperties = cn.uspGetProperties(0,1,1,2,7,12,7,500,2,2,1,"yes")

Where is this particular way of calling stored proc'drs described online?
Thanks in advance.

 

[Tom B]

As a test have you tried
Set rsProperties = cn.Execute("exec uspGetProperties
0,1,1,2,7,12,7,500,2,2,1,'yes'")

 

[Aaron Bertrand - MVP]

Hello all, I'm having a dickens of a time calling a stored procedure on a

connection. Every time I do, it generates an error "Arguments are of the
wrong type, are out of acceptable range, or are in conflict with one
another."

Can you show the definition of your proc ()

Set rsProperties = cn.uspGetProperties(0,1,1,2,7,12,7,500,2,2,1,"yes")

Did you mean (note single quotes, and lack pf parens)

Set rsProperties = cn.uspGetProperties 0,1,1,2,7,12,7,500,2,2,1,'yes'

And did you try

Set rsProperties = cn.execute("EXEC uspGetProperties
0,1,1,2,7,12,7,500,2,2,1,'yes'")

 

[Bob Barrows]

Hello all, I'm having a dickens of a time calling a stored procedure
on a connection. Every time I do, it generates an error "Arguments
are of the wrong type, are out of acceptable range, or are in
conflict with one another." I've run the same stored procedure with
the same exact list of arguments in Query Analyser in SQL Server, and
it works. The page has the adovbs constants. Note that
uspGetProperties is a stored procedure in SQL Server.

Set rsProperties = cn.uspGetProperties(0,1,1,2,7,12,7,500,2,2,1,"yes")

Where is this particular way of calling stored proc'drs described
online? Thanks in advance.

You've got the syntax wrong. First you have to instantiate the recordset
object:
Set rsProperties = server.createobject("adodb.recordset")

Then call the procedure like this:
cn.uspGetProperties 0,1,1,2,7,12,7,500,2,2,1,"yes", rsProperties

This method is described here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdaobj01_7.asp

HTH,
Bob Barrows

 

[dw]

Hello everybody, It works with the cn.Execute method but won't work with the
cn.uspGetProperties. If I remove the parenthesis, it gives error "Expected
end of statement." Also, the recordset is defined further up in the code as
a Server.CreateObject.

Here are the strored procedure's parameters for those items being sent in:

@parmStatus smallint,
@parmDistance smallint,
@parmShuttle bit,
@parmListingType smallint,
@parmHousingType smallint,
@parmLeaseType smallint,
@parmResultDate smallint,
@parmRent int,
@parmBedrooms smallint,
@parmBathrooms real,
@parmFurnished bit,
@parmPets varchar (3)

 

[Bob Barrows]

Have you tried this?

Set rsProperties = server.createobject("adodb.recordset")
cn.uspGetProperties 0,1,1,2,7,12,7,500,2,2,1,"yes", rsProperties

From what I can see it should work. What happens when you try it?

Bob Barrows

 

[dw]

Thanks Bob Barrows! It worked like a charm. No errors, and I got the
results. I thought I had tried that technique sometime today, but maybe I
didn't do it right. Thanks for everybody's help. Happy Fourth! : )