deleting from database

kmf

Hi, i know I'm using asp and access db, but basically its the sql I'm stuck on so I wondered if anyone here would be able to gimme a wee hand?

I'm trying to create an admin page where the user can delete records from the database. The code I have done is:

<%
strcode = TRIM(Request.QueryString("code"))
Set RS8 = CreateObject("ADODB.Recordset")

Set RS8 = con8.Execute ("DELETE FROM equipment WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM exposure WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM info WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM measures WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM nature WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM others WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM substances WHERE (code = 'strcode') ")
Set RS8 = con8.Execute ("DELETE FROM titles WHERE (code = 'strcode') ")

When I use this function I am directed to the page which says that the data has been deleted, but it hasn't. Anyone able to help?

Thanks! 🙂

Lars_Berg

strcode = "'" & replace(TRIM(Request.QueryString("code")),"'","''") & "'" 

con8.Execute "DELETE FROM equipment WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM exposure WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM info WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM measures WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM nature WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM others WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM substances WHERE code = " & strcode ,,128 
con8.Execute "DELETE FROM titles WHERE code = " & strcode ,,128

kmf

Could you explain this to me, if you get a chance?

I just don't like to use code unless I know what its doing! 🙂

Thanks for the quick reply!

Lars_Berg

strcode = "'" & replace(TRIM(Request.QueryString("code")),"'","''") & "'"

the replace is needed for handling the case when code contains an ' in which case that need to be doubled.

Assuming that the querysting contains the value O'brien then the variable strcode will contain the value

'O''brien'

and putting that into the delete statement would yield

con8.Execute "DELETE FROM equipment WHERE code = 'O''brien'",,128

VB-script does not recognize variables in strings (how could it?) so you must use an explicit concatenation.
What happened in your code is that you tried to delete records where the column code equals the string strcode and not the value in the field.

A delete statement does not return any rows and thus there is no need for a recordset. However, ADO will by default create an empty and closed recordset for all statements and the 128 will stop ADO from doing that.

kmf

Thanks very much for explaining it! 🙂

kmf

That worked perfectly, I appreciate the help!

Thanks again!