Can it work: create a stored procedure with output parms, call it with a 
command object and then assign the command object to a record set?  The 
stored proc returns multiple records.

Here's my code--I've created a command object that calls a stored proc 
with output parms.  i want to return multiple records with this stored 
proc.  however, when i set the command object to a record set, the record 
set cannot interpret "do while not adoRs.EOF" (the code freezes at this 
point).  it seems like the combination of returning multiple recs with 
output parms and assigning them to record set does not work.  what's the 
problem here?

set adoCmd = Server.CreateObject("ADODB.Command")
	set adoRs = Server.CreateObject("ADODB.Recordset")
	adoCmd.ActiveConnection = adoCon
	
	adoCmd.CommandText = "{call procGetSplashArticles(?,?,?,?,?,?,?)}"
	

	adoCmd.Parameters.Append adoCmd.CreateParameter 
("newsID",adInteger,adParamOutput,9,strNewsID)
	adoCmd.Parameters.Append adoCmd.CreateParameter 
("title",adVarChar,adParamOutput,100)
	adoCmd.Parameters.Append adoCmd.CreateParameter 
("author",adVarChar,adParamOutput,100)
	adoCmd.Parameters.Append adoCmd.CreateParameter 
("position",adVarChar,adParamOutput,100)
	adoCmd.Parameters.Append adoCmd.CreateParameter 
("location",adVarChar,adParamOutput,100)
	adoCmd.Parameters.Append adoCmd.CreateParameter 
("eventDate",adDate,adParamOutput)
	adoCmd.Parameters.Append adoCmd.CreateParameter 
("textFile",adVarChar,adParamOutput,200)

	adoCmd.ActiveConnection = adoCon
	adoCmd.Execute
	
	set adoRs = adoCmd
	adoRs.MoveFirst
	
	if adoCon.Errors.count > 0 then
		call subProcessAdoError
		call subSendEmailMessage
	else

 		do while not adoRs.EOF 'seems like it couldn't handle eof.

			Response.Write "<p class=subheading><a 
href=./top_nav/news_events_contd.asp?newsID=" & adoRs ("newsID").value 
& ">" & adoRs ("title").value &  "</a></p>"
	
			adoRs.MoveNext
		   
		loop
	end if

http://www.adopenstatic.com/faq/SprocsAndParameters.asp
will help with returning output params and sprocs

Additionally,the following code wont work:

: adoCmd.Execute
:
: set adoRs = adoCmd
: adoRs.MoveFirst

On the first line you are executing the command, which is fine (but the
.Execute will return a recordset, but you're not assigning any variable to
reference this recordset).

On the second line, you are setting adoRS to reference what adoCmd
references (ie the same command object). Then next line is just plain
wrong - the ADO Command object doesn't support .MoveFirst - that's a method
of the ADO Recordset object. adoRS doesn't reference an ADO Recordset
object, it references a command object.

What you need to do is something like:

<%
Set objRS = objCommand.Execute
If not objRS.EOF then
    ' do something with objRS
End If
objRS.Close
Set objRS = Nothing

intMyFirstParam = objCommand.Parameters("@ParamName").Value
%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: <srotondo@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, October 12, 2002 12:08 AM
Subject: [asp_databases] ADO--using stored procs and output parms to return
multiple recs


: Can it work: create a stored procedure with output parms, call it with a
: command object and then assign the command object to a record set?  The
: stored proc returns multiple records.
:
: Here's my code--I've created a command object that calls a stored proc
: with output parms.  i want to return multiple records with this stored
: proc.  however, when i set the command object to a record set, the record
: set cannot interpret "do while not adoRs.EOF" (the code freezes at this
: point).  it seems like the combination of returning multiple recs with
: output parms and assigning them to record set does not work.  what's the
: problem here?
:
: set adoCmd = Server.CreateObject("ADODB.Command")
: set adoRs = Server.CreateObject("ADODB.Recordset")
: adoCmd.ActiveConnection = adoCon
:
: adoCmd.CommandText = "{call procGetSplashArticles(?,?,?,?,?,?,?)}"
:
:
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("newsID",adInteger,adParamOutput,9,strNewsID)
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("title",adVarChar,adParamOutput,100)
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("author",adVarChar,adParamOutput,100)
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("position",adVarChar,adParamOutput,100)
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("location",adVarChar,adParamOutput,100)
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("eventDate",adDate,adParamOutput)
: adoCmd.Parameters.Append adoCmd.CreateParameter
: ("textFile",adVarChar,adParamOutput,200)
:
: adoCmd.ActiveConnection = adoCon
: adoCmd.Execute
:
: set adoRs = adoCmd
: adoRs.MoveFirst
:
: if adoCon.Errors.count > 0 then
: call subProcessAdoError
: call subSendEmailMessage
: else
:
:   do while not adoRs.EOF 'seems like it couldn't handle eof.
:
: Response.Write "<p class=subheading><a
: href=./top_nav/news_events_contd.asp?newsID=" & adoRs ("newsID").value
: & ">" & adoRs ("title").value &  "</a></p>"
:
: adoRs.MoveNext
:
: loop
: end if