Need help for my RunSQL function

[denoxis]

Here is my RunSQL function that allows me to run SQL statement anywhere easily. My problem is the way to identify the statement type i.e. SELECT, UPDATE or INSERT. Because I want to return the affected records if it's update or insert, and also I want to return the recordset and CLOSE the recordset if it's select. If I close the recordset if it's UPDATE it returns an error and doesn't update anything. I wonder if there is a way to check it at object level. Right now it works 99% accuracy, but it looks for substrings. I have to name the stored procedures like "sp_select..." even if it updates something and returns a resultset.

ps. "if (rs.eof and rs.bof) then" doesn't work either to identify if it's an UPDATE.



function RunSQL(SQL)
dim rs,affected, conn

set conn = server.createobject("adodb.connection&quot
conn.Open CONN_STRING

select case left(lcase(SQL),3)

case "sel"
set rs = conn.execute(SQL)
if not (rs.eof and rs.bof) then
RunSQL = rs.getrows()
else
RunSQL = null
end if

case "exe"
if inStr(SQL,"sp_select&quot > 0 then
set rs = conn.execute(SQL)
if not (rs.eof and rs.bof) then
RunSQL = rs.getrows()
else
RunSQL = null
end if
else
set rs = conn.execute(SQL,affected)
RunSQL = affected
end if

case "ins","upd","del"
set rs = conn.execute(SQL,affected)
RunSQL = affected


end select

set rs = nothing
conn.close : set conn = nothing
end function

 

[Tarwn]

Actually the way you have done it above is pretty close to the way I would have done it. I don't see why you would be having problems the other 1% of the time, except for the fact that you don't have any error handling. It might be a god idea to check the error collection on your connection object somewhere in there.

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

 

[denoxis]

One reason is, I want to close the recordset. In some stored procedures, I use UPDATE then SELECT or vice versa. If there is an UPDATE involves it will give me an error (I cannot update if I close the recordset)
If I use only "SET rs = nothing" is this enough for the resources?

Other reason is, I don't want to worry about naming the stored procedures. If I use UPDATE & SELECT type sp, I have to name it sp_select... Otherwise sp_update... etc. It would be nice to handle it without looking into string.

Thanks for the response.