ASP page will complete after insert statement

[militarycoder]

I am fairly new to Web programming and I need some help.
The following code snippet came from a page I wrote. The records are written to the database (SQL Server 2000) but the page never comes up after this point. I have narrowed the problem down to this loop, but I have no idea where to go from here.

SQLStmt="insert into tbl_wss_transpers (fld_ssn,fld_name,fld_adminuic,fld_hirestatus,fld_ ntlogon,fld_subscriptiondate,"
SQLStmt=SQLStmt & "fld_updatedate,fld_source,fld_approved,fld_locked ,fld_update,fld_password) "
SQLStmt=SQLStmt & "values('" & SSN & "','" & NAME & "','" & UIC & "','" & HIRE & "','" & EMAIL & "','" & DTESUB & "',"
SQLStmt=SQLStmt & "'" & DTEUPDT & "','" & SOURCE & "',0,0,0,'" & PASS & "')"
connection.BeginTrans
connection.Execute(SQLStmt)
connection.CommitTrans


set rsName=Server.CreateObject("adodb.recordset")
SQLStmt="select * from tbl_wss_services where fld_active=1 and fld_dpiadmin=0 order by fld_serviceid"
rsName.open SQLStmt,conn
rsName.MoveFirst
While not rsName.EOF
SERVID=rsName("fld_serviceid")
SERVNAME=rsName("fld_servicename")
If Request.Form(SERVID)= "Yes" then
strSQL="insert into tbl_wss_transservice(fld_ssn,fld_serviceid,fld_ser vicename) "
strSQL=strSQL & "values('" & SSN & "','" & SERVID & "','" & SERVNAME & "')"
connection.BeginTrans
connection.Execute(strSQL)
connection.CommitTrans
End If

rsName.Movenext
Wend
rsName.close

Like I said, the records actually make it to the database. The HTML portion of the page is supposed to display the records just written, but page execution stops after the above loop. Also the page takes an inordinate amount of time to load.
Any help would be greatly appreciated.

Thanks

 

[Tarwn]

Is it possible that your not making it all the way through the loop? If the page were timing out after a large number of the records were processed, you would still have processed records in the DB, but you wouldn't have them all (and you wouldn't get past the loop to the output).

Next issue: If this is SQL Server you could speed it up immensely by combining the statements:
sql = "insert into tbl_wss_transservice(fld_ssn,fld_serviceid,fld_ser vicename)" & _
" SELECT '" & SSN & "', fld_serviceid, fld_servicename FROM tbl_wss_services where fld_active=1 and fld_dpiadmin=0 and fld_serviceid IN (" & Request.Form("ids") & ")"

This would negate the need to loop through and do an individual insertion (and transaction) for every record. The only other modification you would need is to change your input on the previous page to pass all the selected service id's in one input name with the value set to the service id, rather than separate names with a 'YES' value (I used the name 'ids' in my example above).

Option 2: If this is not SQL Server or a DB that allows Inserting from a Select, you may want to consider a few methods to speed up your code:
a) Don't select *, just select what you need
b) Use the Recordset.getRows method to dump the recordset to an array
c) Put the entire loop inside a transaction instead of making every insert a transaction. Making a single insert a transaction is a little useless because if it fails, there is nothing to rollback.
d) Use rs = Connection.Execute(sqlStatement) to fill your recordset instead of rs.Open

If your using the array and need to debug, you can add a couple of lines to easily check progress like so (assuming you have a counter called rowCtr to loop through the array):

Response.Write "Processing " & (rowCtr + 1) & " of " (UBound(YourArray,2) + 1) & " rows <br/>"
Response.Flush

Hope this helps,
-T

 

[militarycoder]

I thank you for the help, it did help. But I still have the problem with the page displaying. This what happens:
I have 2 select statements before and 3 or 4 select statements after the two inserts to retrieve the data written to the DB (ALL records do get written to the DB). When there is only 1 service record to write to the DB, everything works fine. If more than 1 record needs to be written, everything slows down (but all records get written) and the page will not display. All I get is a blank page on screen. I put a response.Write below the second insert to check and it will not display. I am lost. Once again thanks

 

[Tarwn]

Adding a response.Flush is key. By default IIS/ASP holds the output in a buffer until the page has finished processing and then sends all of the content. By calling Flush you force the buffer to flush to the end user (the other option is to turn off the buffer, but I don't suggest this).

After calling the Flush you will not be able to use Response.Redirect, Response.Status, etc. as the HTTP headers will have already been sent to the client. However it is very handy for troubleshooting to Response.Flush after a debugging Response.Write.

The quickest way to determine for sure where it is failing is to go ahead and litter the code with Response.Write's. Another handy function is the timer() function. This has millisecond precision (well, 14.5 milliseconds anyway) and can be handy to determine how long code is taking to execute.

Generally I try something along the lines of:

Dim stTime : stTime = timer()

'... lots of code ...
Response.Write (timer() - stTime) * 1000 & "ms - Block 1 Passed"

'... lots of code ...
Response.Write (timer() - stTime) * 1000 & "ms - Block 2 Passed"

'... lots of code ...
Response.Write (timer() - stTime) * 1000 & "ms - Block 3 Passed"

Sorry I can't provide more assistance to directly solve your problem, but I'm a little stumped on this one so far. In the absence of something like a Response.Clear and/or On Error Resume Next you should be getting something back, if not real content than at least an HTTP timeout or a 501 w/ a SQL timeout.
Is there anything unusual in the source for the blank page you receive? Are you using a custom 501 page or changed the timeout for the page?