quick question - what to do with an ms sql record set when an update has been performed.

Factual Questions
1

I think this is too weedy a question to sign up on a website dedicated to sql/asp questions so I’m asking it here where I’m already a member :slight_smile: (and I know there are many fine experts on SQL and ASP)
I have an ASP page on an intranet which basically runs sql queries entered into a html form textarea… I KNOW - mega unwise and unsecure, but it’s a convenient way for me to do sql stuff when I don’t have access to the sql manager (plus the page is only accessable by my own windows account)

Anyway, the page works for select queries, but when I do an update it returns an error from a part of the code trying to check rs.eof.

So my question is, if the sql operations result was “1 records updated” (or whatever it would be after an update) rather than an actual set of records how would I get at that ‘result’ in an asp page in order to check for that condition (to avoid the “Operation is not allowed when the object is closed.” error that is triggered by checking rs.eof?

2

Can you post your ASP code here? If I see it I might be able to help.

3

This isn’t the whole code, but it is the meat of it…



<% if request.form("sql") <> "" then%>

<h3>Results</h3>

<%

SERVER.SCRIPTTIMEOUT = 500
sqlstring=request.form("sql")

set RS = Conn.Execute(sqlstring)
'response.write rs.fields(0).value

If Err.Number <> 0 Then
   Response.Write "An error has occurred!<br>"
   Response.Write "Error number:      " & Err.number & "<br>"
   Response.Write "Error description: " & Err.description & "<br>"
 
'
' **The line below triggers the "Operation is not allowed when the object is closed" error.**
'
'  
  ElseIf RS.EOF Then  
   Response.Write "Empty recordset returned."
   
  Else
   iFields = RS.Fields.Count
   Response.Write "<table border=1><tr>"
   For i = 0 To iFields - 1
    Response.Write "<td><b>" & RS.Fields(i).Name & "</b></td>"
   Next
   Response.Write "</tr>"
   While Not RS.EOF
    Response.Write "<tr>"
    For i = 0 To iFields - 1
     Response.Write "<td>" & RS.Fields(i).Value & "</td>"
    Next
    Response.Write "</tr>"
    RS.MoveNext
   Wend
   response.write "</table>"
  End If
RS.Close
set RS = Nothing
set handle = Nothing
set sqlstring = Nothing
Conn.Close
set Conn = Nothing

end if
%>

It only triggers the error when the sql query generates a message rather than a result set (such as when an update has been performed) I just need to know how to deal with the message in an asp sql result set.

[sub]And just to repeat. The code shows a very unsecure and ‘dangerous’ act being performed - running the text of a html form input directly as an sql query without sanitation. I would never do this on a public page. This page is on an private intranet and the folder has myself and administrators as the only people who can access it from the intranet[/sub]

4

If an update operation returns a closed recordset, and EOF can’t be checked on a closed recordset, then it sounds like you need to check a different attribute/method of the recordset to determine results of last operation.

Did you try looking for other attributes/methods for a recordset? (I haven’t done much asp, so don’t know what the answer is).

5

It’s been many years since I have done any classic ASP but I have an idea that might work:



If Left(sqlstring, 6) = "UPDATE" Then
    Dim RecordsAffected As Long

    Conn.Execute sqlstring, RecordsAffected

    Response.Write RecordsAffected & " records were affected by the UPDATE query..."
ELSE
   set RS = Conn.Execute(sqlstring)

   ...
END IF

The problem you are having is that an UPDATE query does not return a recordset, so that your RS object is NULL. The modified syntax does not involve a recordset object at all for the UPDATE query while remaining unchanged for the SELECT query.

6

This did the trick. Thanks! :slight_smile:

I didn’t use your exact code but I did something similar…


**dim recordsaffected**
set RS = Conn.Execute(sqlstring,**recordsaffected**)
If Err.Number <> 0 Then
   Response.Write "An error has occurred!<br>"
   Response.Write "Error number:      " & Err.number & "<br>"
   Response.Write "Error description: " & Err.description & "<br>"
  **ElseIf recordsaffected > 0 then
	response.write recordsaffected & " records affected"**
  ElseIf RS.EOF Then
7

Can I interest you in a pen test? I swear, it will only take a minute… :smiley:

8

:smiley: . You are welcome to try and get inside the network at my work and then into the folder that only I have access to.
I don’t have any plans to ever do ASP code outside of the context of work. I began with ASP and I now prefer PHP. I still have to look after my (and other people’s) old ASP code though :frowning: so I still have to learn the occasinal bit of ASP knowledge.

eta: the code posted in this thread was built using snippets of someone else’s code. (So I can blame them :smiley: )