Classic ASP Discussion - Newbie is having problem with Delete

Print Page | Close Window

Newbie is having problem with Delete

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=11672
Printed Date: 30 March 2026 at 11:47pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Newbie is having problem with Delete

Posted By: dommett
Subject: Newbie is having problem with Delete
Date Posted: 02 September 2004 at 1:12pm

I have been working thru the asp tutorials on this site and have a problem I don't understand.

I am writing a asp page to delete a recordset from my database. When it executes the line

rsCoupon.Delete

it gives me the follwing error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(ID=Pa_RaM000 AND Company Name=Pa_RaM001 AND Coupon Description IS NULL AND Hyperlink To JPEG=Pa_RaM002 AND Start Date=Pa_RaM003 AND Expiration Date=Pa_RaM004 )'.

I can confirm with Response.Write that I have loaded the correct recordset. I only get the error when I use the delete method. Here's my exact code:

<%
Dim adoCon
Dim rsCoupon
Dim strSQL
Dim lngRecordNo

lngRecordNo = Request.QueryString("ID")

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../fpdb/testcoupon.mdb")

Set rsCoupon = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Main.* FROM Main WHERE ID=" & lngRecordNo

rsCoupon.LockType = 3
rsCoupon.Open strSQL, adoCon
rsCoupon.Delete
rsCoupon.Close
Set rsCoupon = Nothing
Set adoCon = Nothing

Response.Redirect "ShowCoupons.asp"

Replies:

Posted By: Semikolon
Date Posted: 02 September 2004 at 1:20pm

in strSQL try "... WHERE Main.ID="..

OR

after adoCon.Open... use this code:

strSQL = "DELETE FROM Main WHERE Main.ID=" & lngRecordNo



adoCon.Execute(strSQL)



adoCon.Close

Set adoCon = Nothing

This is a better way to delete

Posted By: Semikolon
Date Posted: 02 September 2004 at 1:22pm

also, using spaces is not recommended whatsoever. replace spaces in your field names with for example _

Posted By: dommett
Date Posted: 02 September 2004 at 1:47pm

ok - this worked great. Do I need to do anything to lock this recordset before executing the delete SQL statement or is that handled automatically?

Thanks!

Posted By: Semikolon
Date Posted: 03 September 2004 at 2:47pm

the DELETE statement and adoCon.Execute is all you need

Posted By: pmormr
Date Posted: 06 September 2004 at 2:48am

i try to stay away from adodb.recordset's built in insert, delete, and update functions... it's just so much easier to use SQL statements and then say conn.execute

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: Semikolon
Date Posted: 06 September 2004 at 8:17am

pmormr wrote:

i try to stay away from adodb.recordset's built in insert, delete, and update functions... it's just so much easier to use SQL statements and then say conn.execute

same here.. I use recordsets only when reading data

Posted By: Semikolon
Date Posted: 06 September 2004 at 8:27am

It's actually quite easy:

INSERT:

INSERT INTO <tablename> (<fieldname1, fieldname2,
fieldname3) VALUES ('<field1 string value>', <field2 numeric
value>, <field3 boolean value>)

DELETE:

DELETE FROM <tablename> WHERE
<tablename>.<fieldname> = '<text
value>'/<numeric/boolean value>

UPDATE:

UPDATE <tablename> SET <fieldname1> = '<new field1
string value>', <fieldname2> = <new field2 numeric
value>, <fieldname3> = <new field3 boolean value> WHERE
<fieldname> = '<text value>'/<numeric/boolean
value>

This is what you need in most cases. when you get more advanced, you may need some more advanced queries, but then I recommend you to buy a good SQL book..

BTW, this time I actually pressed Preview before I posted in case there were something wrong in my post!