Problems deleting from database 2

[nicBee]

Hi

i have a database with one table which has 2 fields a text field which is the primary key and a memo field. I have a script which is supposed to connect to the database, select a recordset according to a querystring submitted from another page and delete it. here is the code for this.

dim ref
ref = request.querystring("title")

'Connect to db
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
sConnString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/Databases/myDB.mdb") & ";"
conn.Open(sConnString)

dim sqlDel
sqlDel = "SELECT * FROM Vacancies WHERE Title = '" & ref & "'"

response.write sqlDel & "<br><br>"

dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.LockType = 3
rs.open sqlDel, conn, , , adCmdText
rs.Delete

However this doesnt work. i keep getting an error saying "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record"

I have printed out the ref variable and it contains the correct value. i have tried replacing this line

sqlDel = "SELECT * FROM Vacancies WHERE Title = '" & ref & "'"

with sqlDel = "SELECT * FROM Vacancies WHERE Title = 'ref1'" where ref1 is a field in the database and it works fine, so i think it is something to do with the quotation marks which are put on either side of the value the ref variable contains, as when i print out the sql query it looks like the query below!

SELECT * FROM Vacancies WHERE Title = '"ref1"'

does anyone know how i can prevent this happening so my query will work, it is driving me crazy!!

thanks in advance

 

[PHV]

Have you tried this ?
ref = Replace(request.querystring("title"),Chr(34),"")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nicBee]

Oh that worked. Thank you so so much - youre a star!

 

[EBGreen]

Then you should give him a star.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[nicBee]

i will - didnt realise you could do that!