Getting data from a SP in ASP document.

Question

Getting data from a SP in ASP document.

Ten Centuries

Points: 1028

June 14, 2004 at 12:23 pm

I'm having a problem with getting data back from an sp that is run from an ASP page
My sp contains a transaction that inserts then updates data, followed by a select to present
the results of the transaction back to the user. i.e. My sp looks roughly like thiscreate procedure dbo.mysp @params... as
    begin transaction
        insert into table1 select stuff from table2
        update table1 set stuff=other stuff
    commit transaction
    select stuff from table1
When run from query analyzer, the results look like...(1 rows(s) affected)
(1 rows(s) affected)
col1 col2 col3
---- --- ----
data....
(4 rows(s) affected)
I normally get data back into my asp page with code likeset conn=server.createobject("ADODB.Connection")
conn.open "datasource;"
set rset=server.createobject("ADODB.Recordset")
rset.activeconnection=conn
rset.open "EXEC mysp @param=value"
do while not rset.eof
    for each column in rset.fields
        response.write column.value & ",&nbsp;" & chr(10) & chr(13)
    next
    rset.movenext
loop
rset.close
conn.closeHowever, this code is resulting in an infinite loop on the server, broken
only by the server timeout! I can't figure out what's wrong!
 
Graham

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

click-fund Ten Centuries Points: 1028 More actions · Answer 1

p.s. when I first posted this vbs snippet I forgot to include the "rset.movenext" call. I know that this needs to be there, that's not the problem.

jordanac Hall of Fame Points: 3653 More actions · Answer 2

Just a shot in the dark....Do you use SET NOCOUNT ON in your SP?

Andy.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

Here's my two penneth.

Make sure that you are getting back the correct amount of records by bringing back a record count.

If it looks OK, it's worth having a look at the For each column statement. Try:

intNumColumns = rset.ColumnCount

For inti = 0 To intNumColumns - 1

response.write column.value & ", " & chr(10) & chr(13)

Next inti

Hope this helps you out

------------------------------
The Users are always right - when I'm not wrong!

click-fund Ten Centuries Points: 1028 More actions · Answer 4

Well, that might help getting ADO to pick the right result set. I'll try it.