Passing Parameters to a Stored Procedure using ASP

Question

I'm trying to pass some parameters to a SQL stored procedure in my classic ASP. I've seen several posts on this and not sure what I'm doing wrong as I don't seem to see my discrepancy.

set conn = CreateObject("ADODB.Connection") 
conn.open ("DSN=SERVER;UID=username;PWD=pwd;Database=MyDatabase")

 set cmd = Server.CreateObject("ADODB.Command")
 set cmd.ActiveConnection = conn
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = my_proc
 cmd.Parameters.Refresh
 cmd.Parameters(1) = "MyParam"

set rs = cmd.execute

I'm getting the error

Arguments are of the wrong type, are out of acceptable range, or are in conflict 
with one another.

on the line cmd.CommandType = adCmdStoredProc. I also tried to do it the following way with the same error

set conn = CreateObject("ADODB.Connection") 
conn.open ("DSN=SERVER;UID=username;PWD=pwd;Database=MyDatabase")

 set cmd = Server.CreateObject("ADODB.Command")
 set cmd.ActiveConnection = conn
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = my_proc
 cmd.Parameters.Refresh
 cmd.Parameters.Append cmd.CreateParameter("@MyParam, adVarWChar, adParamInput, 50, "test")

set rs = cmd.execute

score 1 · Answer 1 · edited May 23 '17 at 11:43

@KekuSemau is correct but let me a suggest a more efficient and manageable approach then using the adovbs constants file.

METADATA allows you to define a reference to the DLLs constants even if your using Late Binding as is the case in a Classic ASP environment. It's probably worth mentioning that you can add METADATA references in individual pages but then again why would you?

To use it simply add the METADATA tag to your global.asa file (should be located in the root of your Web Application).

<!-- 
METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ADO\msado20.tlb"
-->

Depending on the system the ADO Type Library maybe different, adjust the FILE attribute accordingly.

I use this approach in all my applications mainly for referencing constants in the ADO and CDO Type Libraries.

ADO Type Library

<!--
METADATA 
TYPE="typelib" 
FILE="c:\program files\common files\system\ado\msado15.dll"
-->

CDO Type Library

<!-- 
METADATA 
TYPE="typelib" 
UUID="CD000000-8B95-11D1-82DB-00C04FB1625D" 
NAME="CDO for Windows 2000 Library"
-->

These are examples of my own personal references, file locations maybe different whereas UUID attributes should be exactly the same.

IMPORTANT:

Remember to remove any references to adovbs constant include file (adovbs.inc or adovbs.asp commonly) when using METADATA approach in your global.asa or you will get a
Name redefined error
Also METADATA is only available in IIS 4.0 and above.

Useful Links

Using METADATA to Import DLL Constants (Recommended reading)

@AlexK. Yep, you can use either `FILE` or `UUID` to reference the Type Libraries. — user692942, Nov 27 '14 at 11:39

score -1 · Accepted Answer · answered Nov 05 '14 at 20:06

-1

You use ADO through late binding, which means that the constants like adCmdStoredProc, adParamInput etc. are unknown to your code (so they are always 0).

You can either look them up and define the ones you need as a constant in your code (or use the numbers directly, but that's not well readable if you edit the code again later).

Or take a look here on msdn - you may find a file that defines all the constants at
c:\Program Files\Common Files\System\ado\adovbc.inc.

answered Nov 05 '14 at 20:06

KekuSemau

6,830
4
24
34

@doby48 No don't do this, using `adovbc.inc` is a poor substitute for [META Type Library references](http://www.4guysfromrolla.com/webtech/110199-1.shtml). Added the META Type Library reference is a once only (as it's added to the `global.asa` in your Classic ASP application), whereas `adovbc.inc` has to be included in every page or set in another `#include` file to apply it globally. – user692942 Nov 06 '14 at 09:31

score -1 · Answer 3 · edited Nov 06 '14 at 12:10

-1

I think there is only a very small piece that you are doing wrong:

set conn = CreateObject("ADODB.Connection") 
conn.open ("DSN=SERVER;UID=username;PWD=pwd;Database=MyDatabase")

set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = conn
cmd.CommandType = 4 ' adCmdStoredProc constant is not defined in your context
cmd.CommandText = my_proc
cmd.Parameters.Refresh
cmd.Parameters(1).value = "MyParam"

set rs = cmd.execute

The ADO constants are probably undefined, and also (but not sure) the parameter should be assign via its value property.

edited Nov 06 '14 at 12:10

user692942

16,398
7
76
175

answered Nov 05 '14 at 20:08

MaxiWheat

6,133
6
47
76

This will not work as the `Parameters(1)` has not been defined in the collection (Use `.CreateParameter()` to define it), also the `.Parameters` collection is ordinal starting at `0`. – user692942 Nov 06 '14 at 10:08
`.Refresh` gets the parameters from the server and adds them to the `.Parameters` collection – MaxiWheat Nov 06 '14 at 11:55
Sorry missed that. I've had mixed results with `Refresh` in the past prefer to declare them myself. – user692942 Nov 06 '14 at 12:10
Also worth noting that `Refresh` is not recommended for production environments due to it requiring another round trip to the server to complete the call and pull back the parameter definitions. – user692942 Feb 24 '16 at 07:53

Passing Parameters to a Stored Procedure using ASP

3 Answers3

Useful Links

Linked