Returning result of a Stored Procedure - object error

[Streetdaddy]

1.
I am using ADO to pass an SQL string that executes a stored procedure. The stored proc returns a single value in a named column. If i run the code it executes the sp, but get an ADO error if I try to output the value e.g.

Response.Write oRecordSet("next_id&quot

If I run the sp in sql query analyzer it returns the value everytime.

2.
It actually works fine on win2k + IIS 5 with , but the error occurs on NT4 + IIS4. I checked and the ADO version on the NT4 server is version 1.5. The version on win2k server is 2.5.
Miles
busymiles@lan-house.net

 

[JuanitaC]

Could you post the code that executes the stored procedure and attempts to access the returned value?

 

[Streetdaddy]

                  'First check availability of CSO
			strSQL = "SELECT id FROM cso WHERE insession = 0 AND logged_in = 1"
			OpenConnection()
			ExecuteSQL(strSQL)
			
			if oRecordSet.EOF then
				'no cso available
				queueString = "&queue_position=none"
			else
				'cso available
				queueString = ""
			end if
			
			'send request to database
			strSQL = "EXECUTE RequestChat " & Session("user_id")
			'strSQL = "INSERT INTO discussion(user_id) VALUES (" & Session("user_id") & ");SELECT TOP 1 id as next_id FROM discussion WHERE user_id = " & Session("user_id") & " ORDER BY datestamp_requested DESC"
			'OpenConnection()
			ExecuteSQL(strSQL)
			
			if oRecordSet.EOF then
				location = "holdframe.asp?did=error"
			else
				location = "holdframe.asp?did=" & oRecordSet("next_id")
			end if
			
			CloseConnection()

Miles

Those Micros~1 guys sure know what they doing!

 

[Jonax]

Try replacing EXECUTE RequestChat
with EXECUTE PROCEDURE RequestChat This is not a bug - it's an undocumented feature...
;-)

 

[harmmeijer]

Without the sproc and the ExecuteSQL the only thing we know is that you execute a sproc called RequestChat with an input parameter.

You can upgrade your ado using mdac (Micros... Data Access Components) Downloadable by MS. Mdac 2.7 is for sql server 2000 and some distributed stuff wouldn't work like you'd like to if you use an older SQL server version.

You can allso check this thread on how to retreve output parameters form a sproc.

thread222-273886

 

[Streetdaddy]

Thanks everyone

I wanted to avoid having to update the clients MDAC, although it turns out that live server had the latest MDAC and it all worked fine! Silly client...

At first I just did the easy thing and put the SQL from the sproc into the page. That worked, but it was a hack!

I'll try EXECUTE PROCEDURE just for curiosity's sake Miles

Those Micros~1 guys sure know what they doing!