Stored Proc as native connection object method

[Stefan Berglund]

I'm having difficulty making a stored procedure with multiple input parameters work as a
native method of the connection object. The only way I could get it to work was as below
and I'm wondering if this falls into the category of dynamic SQL and thereby negates the
value of the stored proc. I'm fairly certain that I've tried just about every permutation
possible and unless I've failed with the magic search criteria Google is far from helpful
in this area for some reason.

Dim strShowID
strShowID = 320
Dim strSort
strSort = "Owner"
Dim rs
Set rs = cn.Execute("exec show_ShowEntries " & strShowID & "," & strSort)

or alternatively

Set rs = CreateObject("adodb.recordset")
rs.Open "show_ShowEntries " & strShowID & "," & strSort, cn, , adCmdStoredProc

The following code works in VB using all variants and late binding:

Set rs = CreateObject("ADODB.Recordset")
cn.show_ShowEntries strShowID,strSort,rs

but yields this error message when used on an ASP.

Parameter object is improperly defined. Inconsistent or
incomplete information was provided.


For the record the stored proc looks like this:
CREATE PROCEDURE show_ShowEntries @ShowID INT, @Sort VARCHAR(7)='Number' AS
SET NOCOUNT ON

etc.

 

[Bob Barrows]

Set rs = CreateObject("ADODB.Recordset")
cn.show_ShowEntries strShowID,strSort,rs

but yields this error message when used on an ASP.

Parameter object is improperly defined. Inconsistent or
incomplete information was provided.


For the record the stored proc looks like this:
CREATE PROCEDURE show_ShowEntries @ShowID INT, @Sort
VARCHAR(7)='Number' AS
SET NOCOUNT ON

Have you verified that strShowID and strSort both contain values? The only
way I can reproduce this error is if I fail to provide a value for either
strShowID or strSort. If you do not wish to pass a value for strSort, set it
to Null:

strShowID = 28
strSort = null
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open strConn

Set rs = CreateObject("ADODB.Recordset")
conn.show_ShowEntries strShowID,strSort,rs

HTH,
Bob Barrows

 

[Stefan Berglund]

On Tue, 21 Oct 2003 16:59:06 -0400, "Bob Barrows"

Have you verified that strShowID and strSort both contain values? The only
way I can reproduce this error is if I fail to provide a value for either
strShowID or strSort. If you do not wish to pass a value for strSort, set it
to Null:

strShowID = 28
strSort = null
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open strConn

Set rs = CreateObject("ADODB.Recordset")
conn.show_ShowEntries strShowID,strSort,rs

HTH,
Bob Barrows

That was it Bob, the second parameter was empty on the first
pass. I wrongly assumed that procedure's default value would
override that. Thanks again.