Help--storedproc won't generate value. works in query analyzer.

[TeaAddictedGeek]

I did a check on this and it claims that it's BOTH BOF and EOF (you got me how). When I do a response.write on the storedproc and run it in the query analyzer, it does indeed produce output. But when I try to even print out the results to the screen from the code, it tells me: <br>
<br>
ADODB.Field error '800a0bcd' <br>
<br>
Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record. <br>
<br>
<br>
I'm at my wits end. Please help. I've tried everything that I can think of. The storedproc is VERY simple, and is in the identical format of every other storedproc on the page and other pages. Something with the variables is really off, and this is my SECOND attempt to make this page work--the first attempt was a comparison of strings, and even when they were identical they claimed it wasn't. <br>
<br>
Here's the code:<br>
<br>
Set dbObj = Server.CreateObject( &quot;ADODB.Connection&quot; ) <br>
dbObj.ConnectionString = Application( &quot;connectionString&quot; ) <br>
dbObj.Open <br>
storedproc = &quot;AM_GetID @Name='&quot; + cstr( Name ) + &quot;'&quot; <br>
'response.write (storedproc):response.end <br>
Set rsObj = dbObj.Execute( storedProc, intRecs, adCmdStoredProc ) <br>
<br>
<br>
ID = rsObj(&quot;ID&quot <br>
response.write(ID) <br>
<br>
Like I said, if I turn off the comment for the response.write and copy/paste into the query analyzer, the stupid query works. It isn't the query.<br>
<br>
<br>

 

[jfriestman]

From your ASP code, it looks to me like your SP is designed to return an integer ID rather than a recordset. If you are trying to return OUTPUT parameters from the SP, you must use the Command object.<br>
<br>
I'm assuming your SP looks something like this:<br>
CREATE PROCEDURE AM_GetID<br>
@Name Varchar(10),<br>
@ID SmallInt OUTPUT<br>
AS<br>
SELECT @ID = (SELECT id FROM myTable WHERE name = @Name)<br>
GO<br>
<br>
That being the case and you want the ID field, try this:<br>
<br>
'change the Param datatypes (adVarChar(10) and adSmallInt) to the datatypes and names expected by your SP.<br>
<br>
Dim Cmd<br>
Set Cmd = Server.Createobject(&quot;ADODB.Command&quot<br>
With Cmd<br>
.ActiveConnection = Application( &quot;connectionString&quot; )<br>
.CommandType = adCmdStoredProc<br>
.CommandText = &quot;AM_GetId&quot;<br>
.Parameters.Append .CreateParameter(&quot;@Name&quot;, adVarChar, adParamInput, 10, Name)<br>
.Parameters.Append .CreateParameter(&quot;@ID&quot;, adSmallInt, adParamOutput)<br>
.Execute<br>
End With<br>
<br>
Dim id<br>
id = Cmd.Parameters(&quot;@ID&quot.Value<br>
Response.Write id<br>
<br>
<br>
Hope this helps.<br>
<br>

 

[TeaAddictedGeek]

Nope, the query isn't the problem. The problem is the variable being passed.<br>
<br>
Perhaps I didn't make myself clear... The variable I'm trying to pass is a string, it comes from another query. When I compare this string to another string that is identical, it claims they are totally different. It is this messed up variable which is causing this query not to work.<br>
<br>
As stated before... the query works! There's nothing wrong with the query. There's something very wrong with this stupid variable.