SQL records count before executing

[MikeT]

I made a page to execute sql statements on a MS-SQL 7 db and an Access db. I'd like to set it up so that if 10 or more records will be affected by UPDATE statements, the user would get some sort of confirmation stating 'Warning- 45 records will be affected' or something similar. I'd like to have the exact amount of records. The user would then click a link or button, and the statement would execute if so desired.

Can this be done?

 

[HowardMarks]

Not sure if this is most efficient way to do this, but you could just use a select statement first to grab the records which match. Then loop through the recordset and count the number of records, which gives you your value.
Then, once the user has confirmed the action, just execute the update.

Hope this helps ;-) Nick (Software Developer)

http://www.retrographics.co.uk

http://www.retrotest.co.uk

nick@retrographics.fsnet.co.uk
nick.price@myenable.com

 

[MikeT]

Thats a good idea, but won't work in my case. The user types the sql statement into a text box, and clicks Submit, the sql statement is executed. The conversion of the UPDATE statement to a SELECT statment would have to be done via a script.

I don't think this can be done reliably (imagine the number of syntax possibilies). I thought there was some function built into ADODB or MDAC or ASP that you can access the kind of info I need.

 

[MikeT]

I've found this-

recs=0
con.execute sql, recs

where recs is now the number of records affected. The only problem is this happens *after* the statement is executed.

Is there a way to do this before?

 

[GaryC123]

Dunno how but maybe you could do this with a disconnected recordset, so nothing happens to the database until you've got the confirmation. But sry cant help you further Saturday 12.00

20.00

3.00am

Sunday [img http

 

[GaryC123]

Found this, maybe you can tie it into the disconnected recordset

http://www.readthefuckingmanual.co.uk/articles/article_12.asp

Saturday 12.00

20.00

3.00am

Sunday [img http

 

[Tarwn]

Why not just parse the user string out to get the portion of the update that starts with "WHERE.." and the table names, than execute an SQL statement to count the records, if the number is > 10 don't execute their update, ask them to confirm, if the number is less than ten than execute the update.

Dim userWHERE, userTables
userSQL = right(len(Request.Form("userEntryField")) - InStr(Request.Form("userEntryField"),"WHERE")-1)
userTables = mid(Request.Form("userEntryField"),4,InStr(Request.Form("userEntryField"),"SET")-4)

Dim sql_count, rs_count
sql_count = "SELECT Count(*) as record_count FROM " & userTables & userWHERE

'execute the sql statment into rs_count

If rs_count("record_count") > 10 Then
   %>
   You have selected <%=rs_count(&quot;record_count&quot;) %> records for update, please confirm this:
   <form method=POST action=&quot;pagename.asp&quot;>
   <input type=&quot;hidden&quot; name=&quot;UserEntryField&quot; value=&quot;<%=Request.Form(&quot;userEntryField&quot;)%>&quot;>
   <input type=&quot;hidden&quot; name=&quot;UserConfirm&quot; value=&quot;true&quot;>
   <input type=&quot;submit&quot; value=&quot;Confirm&quot;>
   </form>
   <%
Else
   'execute the user statement
End If

This is all on the fly so needs some work, but the logic should be solid,
-Tarwn "The problem with a kludge is eventually you're going to have to back and do it right." - Programmers Saying (The Wiz Biz - Rick Cook)
"Your a geek!" - My Girlfriends saying