[thelist] asp recordset loop

Brian Cummiskey Brian at hondaswap.com
Wed Aug 17 09:38:19 CDT 2005

I sent a post last night about a cursor loop problem.  I never got it, 
so i don't even know if it made it to the list or not.  If you saw it, 
great.  if not--  disregard.

Turns out, it was working fine all along.  it was my web-based asp 
interface that doesn't seem to work.  Here goes...

The SP when triggered manually in query analyzer, works fine.  I get my 
multiple result sets via the cursor selecting from multiple specific tables.

sp_MULTI_DNC 'ALL', 'company', 'ACME', NULL, NULL, NULL
procedure - thejob - flow_type - company - first_name - last_name - 

when i call it in an asp loop, i don't get the desired results--  it 
stops after the first job, which is why yesterday it had me thinking 
that the cursor wasn't working properly.

here's my asp:

if theserver = "MBC2" OR theserver = "ALL" then
	server.ScriptTimeout = 3600
	set cnn = server.CreateObject("ADODB.connection")
	set comm = Server.CreateObject ("ADODB.Command")
	with comm
		.CommandTimeout = 0
		.ActiveConnection = connstr2
		.CommandText = "sp_MULTI_DNC"
		.CommandType = adcmdstoredproc
		.parameters("@thejob").value = thejob			
		.parameters("@flow_type").value = flow_type
		.parameters("@company").value = company
		.parameters("@first_name").value = first_name
		.parameters("@last_name").value = last_name
		.parameters("@phone_number").value = phone_number
		set rst = Server.CreateObject("ADODB.Recordset")
		set rst = .execute

		if not rst.eof then
			dim tableheaders
			tableheaders = ""

			dim tablebody
			tablebody = ""

				tableheaders = tableheaders & "<table style=""border-collapse: 
collapse;"" border=""1"" cellspacing=""0"" cellpadding=""2"">" & vbcrlf
					for x = 0 to rst.Fields.count - 1
						tableheaders = tableheaders & "<th>" & trim(rst.Fields(x).Name) & 
"</th>" & vbcrlf
				do until rst.eof
					tablebody = tablebody & "<tr>" & vbcrlf
					for x = 0 to rst.Fields.count - 1
						tablebody = tablebody & "<td>" & trim(rst.Fields(x).Value) & 
"</td>" & vbcrlf
					tablebody = tablebody & "</tr>" & vbcrlf	
				tablebody = tablebody & "</table>" & VbCrLf
				tablebody = tablebody & "<h3>no results</h3>"
		end if
end if

and later in the body part-

<div id="main">
	<form id="frmdefault">
		<%= tableheaders %>
		<%= tablebody %>		
		<input type="hidden" name="ref" id="ref" value="do_lookup.asp" />

the end result, will have a check box next to the record to select and 
modify/etc on the next page.

anyone know why my loop stops?

does the record set end after the first job is complete?

More information about the thelist mailing list