deleting rows with autonumber

[dauzat]

Hello everyone, I am still having trouble deleting a row from my database. I am creating a employment site, which is used to list job openings. In order for the user to delete the job opening after the position is taken, I have set up Access 2000 to automatically assign every job opening an ID number. My problem is that I can't make it delete by this ID number, Here is the code I am using and a copy of the error message I am receiving. I would greatly appreciate any help on this matter.

' *** Creating Connection Object and opening the database ***
data_source= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("../../data/emp.mdb&quot
SQLStmt = "DELETE DISTINCTROW lngID FROM employment WHERE lngID = " & lngID & &quot"
Set con = Server.CreateObject("ADODB.Connection&quot
con.Open data_source
con.Execute SQLStmt


Microsoft JET Database Engine error '80040e14'

Extra ) in query expression '(lngID =)'.

/HuntGuillot/hg-remove2.asp, line 120

 

[dawnb]

Have you tried removing the & &quot" from the end of the sql statement? You should end up with

SQLStmt = "DELETE DISTINCTROW lngID FROM employment WHERE lngID = " & lngID

I always find that a good way to debug SQL statements is to temporarily put in

response.write SQLStmt
response.end

before the line that executes the SQL. This way, you will be able to read the SQL that is being passed to your database.

If you are still missing the error when reading it, copy the statement that has been written out to the browser and open your database program and run the statement as a query. This should give you a better error message. If it works OK, then the SQL is not the problem.

Good luck,
dawn dawn@soholondon.com