468,242 Members | 1,888 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

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.
Jul 19 '05 #1
6 6607
As a test have you tried
Set rsProperties = cn.Execute("exec uspGetProperties
0,1,1,2,7,12,7,500,2,2,1,'yes'")

"dw" <co***************@uncw.edu> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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.

Jul 19 '05 #2
> 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'")

--
www.aspfaq.com
Jul 19 '05 #3
dw wrote:
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...mdaobj01_7.asp

HTH,
Bob Barrows

Jul 19 '05 #4
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)

"Cowboy (Gregory A. Beamer)" <No************@comcast.netRemuvThis> wrote in
message news:ui**************@TK2MSFTNGP10.phx.gbl...
The norm is to create the command and then attach parameters. Each parameter needs a separate parameter object and all params are added to the params
collection for the command object.

NOTE: I have not done traditional ASP in awhile; you will have to tweak the code to get it to run. This is just to show you the basics:

Dim conn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim rs 'As ADODB.Recordset
Dim param 'As ADODB.Parameter

Set conn = Server.CreateObject("ADODB.Connection")
Call conn.Open("connection string here")

Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandType = adCmdStoredProc '4 if you are using numbers

Set param = Server.CreateObject("ADODB.Parameter")
param.Name = "@ParamName"
param.Value = "value here"
param.Direction = adParamInput '1 if you are using numbers

cmd.Parameters.Add(param)

'Do for all other parameters

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

************************************************** ************************** ****
Think Outside the Box!
************************************************** ************************** ****
"dw" <co***************@uncw.edu> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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.


Jul 19 '05 #5
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 wrote:
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)

"Cowboy (Gregory A. Beamer)" <No************@comcast.netRemuvThis>
wrote in message news:ui**************@TK2MSFTNGP10.phx.gbl...
The norm is to create the command and then attach parameters. Each
parameter needs a separate parameter object and all params are added
to the params collection for the command object.

NOTE: I have not done traditional ASP in awhile; you will have to
tweak the code to get it to run. This is just to show you the basics:

Dim conn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim rs 'As ADODB.Recordset
Dim param 'As ADODB.Parameter

Set conn = Server.CreateObject("ADODB.Connection")
Call conn.Open("connection string here")

Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandType = adCmdStoredProc '4 if you are using numbers

Set param = Server.CreateObject("ADODB.Parameter")
param.Name = "@ParamName"
param.Value = "value here"
param.Direction = adParamInput '1 if you are using numbers

cmd.Parameters.Add(param)

'Do for all other parameters

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

************************************************** **************************
****
Think Outside the Box!

************************************************** **************************
****
"dw" <co***************@uncw.edu> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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.

Jul 19 '05 #6
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! : )

"Bob Barrows" <re*******@yahoo.com> wrote in message
news:%2******************@tk2msftngp13.phx.gbl...
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 wrote:
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)

"Cowboy (Gregory A. Beamer)" <No************@comcast.netRemuvThis>
wrote in message news:ui**************@TK2MSFTNGP10.phx.gbl...
The norm is to create the command and then attach parameters. Each
parameter needs a separate parameter object and all params are added
to the params collection for the command object.

NOTE: I have not done traditional ASP in awhile; you will have to
tweak the code to get it to run. This is just to show you the basics:

Dim conn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim rs 'As ADODB.Recordset
Dim param 'As ADODB.Parameter

Set conn = Server.CreateObject("ADODB.Connection")
Call conn.Open("connection string here")

Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandType = adCmdStoredProc '4 if you are using numbers

Set param = Server.CreateObject("ADODB.Parameter")
param.Name = "@ParamName"
param.Value = "value here"
param.Direction = adParamInput '1 if you are using numbers

cmd.Parameters.Add(param)

'Do for all other parameters

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

************************************************** **************************
****
Think Outside the Box!

************************************************** **************************
****
"dw" <co***************@uncw.edu> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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.


Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Zlatko Matić | last post: by
2 posts views Thread by Dino L. | last post: by
2 posts views Thread by Woody Splawn | last post: by
9 posts views Thread by Problematic coder | last post: by
2 posts views Thread by =?Utf-8?B?YW5vb3A=?= | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.