Stored procedure..? 2

[kenjoswe]

Hi all,
I'm trying to figure out how to retrive records with a stored procedure. Below is what I have done so far:

Set StrConn=Server.CreateObject("ADODB.Connection&quot
StrConn.open "DbName","User","Pwd"
set rs=Server.CreateObject("ADODB.Command&quot

With rs
.ActiveConnection=StrConn
.CommandText="sp_ProcName"
.CommandType=adCmdTable
.Execute ,,
end with

What have I missed?

/Kent J.

 

[link9]

.CommandType=adCmdTable

should be:

.CommandType=adCmdStoredProcedure

or

.CommandType=4

Your choice.


Paul Prewett

 

[kenjoswe]

Thanks.
But it seems there is something wrong with 'Execute , ,'

/Kent J.

 

[link9]

Have you tried my solution? What error does it throw?

 

[kenjoswe]

I'll get:

Syntax error

.../Test.asp, line 23

.Execute ,,
-----------^

/Kent J.

 

[link9]

Try taking off those commas, but that shouldn't make any real difference --

lemme know -
paul

 

[Geee]

This is the code I use to execute a SQL stored procedure...

<%
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;DNSName&quot;, &quot;DNSUserName&quot;, &quot;DNSPassword&quot;
'Create the Connection Object

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
'Create a RS Object
set cmd11 = Server.CreateObject(&quot;ADODB.Command&quot
'Create a Command Object

set p = cmd.Parameters
p.Append cmd.CreateParameter(&quot;@v_Variable&quot;, 200, 1,64)
cmd.ActiveConnection = conn
cmd.CommandText = &quot;spStoredProcedureName&quot;
cmd.CommandType = 4
cmd.Parameters.Refresh
cmd(&quot;@v_Variable&quot = Request.Form(&quot;Value&quot
set rs = cmd.Execute
%>

Hope this Helps...

Gary
-GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[kenjoswe]

Then I get:
ADODB.Command error '800a0bb9'
The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.

../Test.asp, line 22
(which is .CommandType=adCmdStoredProc)

/Kent J.

 

[link9]

have you declared your constants or included the adovbs.inc file so that you can use your constant enum's???

If not, then set it = 4 and see if that clears it up --

If it's a stored procedure, and your .commandtext is definitely set to a valid stored procedure name that exists in your database, then you definitely need to use the .commandtype of a stored procedure, not a table.

 

[Geee]

If your SP has no variables, then try this...

<%
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open &quot;DNSName&quot;, &quot;DNSUserName&quot;, &quot;DNSPassword&quot;
'Create the Connection Object

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
'Create a RS Object
set cmd11 = Server.CreateObject(&quot;ADODB.Command&quot
'Create a Command Object

set p = cmd.Parameters
cmd.ActiveConnection = conn
cmd.CommandText = &quot;spStoredProcedureName&quot;
cmd.CommandType = 4
cmd.Parameters.Refresh
set rs = cmd.Execute
%>


Works for me every time!

Hope this helps

Gee

-GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[kenjoswe]

Thanks everybody!

Now it works.

/Kent J.