Delete entries on Access database with ASP

[jazzpiano]

Hello - newbie here,

I have a variety of ASP files managing a database and one of them should help delete entries on the database. There is a bug in the code and I would be grateful if anyone could help.

Code =
<%

DIM PathToDatabase
PathToDatabase="D:\Domains\directionsatwork.co.uk\wwwroot\cgi-bin\DATABASE.mdb"

DIM NameOfTableInDB
NameOfTableInDB="Table1"

Set DB = Server.CreateObject("ADODB.Connection")
DB.mode = adModeReadWrite
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)
For Each x In Request.Form
DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id=" & x)
Next
Set RS = Nothing
Set DB = Nothing
Response.redirect("dbcontrol.asp")
%>

Error message is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Id=value='.

/cgi-bin/dbdelete.asp, line 13

[nbcrockett]

Try changing this line:

DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id=" & x)

To this:

DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id='" & x & "'")

[jazzpiano]

Thanks for the reply.

I have just tried that but still get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Id=value='.

/cgi-bin/dbdelete.asp, line 13

[russell]

would need to see your form, but here is the basic problem

For Each x In Request.Form
DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id=" & x)
next

For Each x In Request.Form
DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id=" & Request.Form(x))
next

if u still have trouble, show us the form code.

:)

[jazzpiano]

Thank you but I am afraid that still doesn't work. I would be very grateful if you could take a look.

Here are the three relevant forms:

dbcontrol.asp:

<%

DIM ColunmNameIntable(6)


ColunmNameIntable(0)="Id"
ColunmNameIntable(1)="Data1"
ColunmNameIntable(2)="Data2"
ColunmNameIntable(3)="Data3"
ColunmNameIntable(4)="Data4"
ColunmNameIntable(5)="Data5"
ColunmNameIntable(6)="Data6"

' When displaying data we will use this values
DIM DataName(6)

DataName(0)="Id"
DataName(1)="Role"
DataName(2)="Industry"
DataName(3)="Location"
DataName(4)="Salary"
DataName(5)="Status"
DataName(6)="More detail"%>

<FORM action=dbaddrecord.asp method=post>
<input type=hidden name=action value=add><br>
<% =DataName(1) %>: <input type=text name="<% =ColunmNameIntable(1) %>"><br>
<% =DataName(2) %>: <input type=text name="<% =ColunmNameIntable(2) %>"><br>
<% =DataName(3) %>: <input type=text name="<% =ColunmNameIntable(3) %>"><br>
<% =DataName(4) %>: <input type=text name="<% =ColunmNameIntable(4) %>"><br>
<% =DataName(5) %>: <input type=text name="<% =ColunmNameIntable(5) %>"><br>
<% =DataName(6) %>: <input type=text name="<% =ColunmNameIntable(6) %>"><br>

<input type=submit value=submit></form>
<HR>

<%
DIM DB
DIM RS
DIM data1st
if request.querystring="1" then

DIM PathToDatabase ' The location of our database within our server
PathToDatabase="D:\Domains\directionsatwork.co.uk\wwwroot\cgi-bin\DATABASE.mdb"

DIM NameOfTableInDB ' The name of the table in our Access database. In our case this name is "Table1"
NameOfTableInDB="Table1"

' The name of each colunm in the table is contained in the array "ColunmNameIntable".
' The sample database provided in this tutorial has up to 20 colunms
' with names "Data1" to "Data20" (and the first colunm, which is named "Id"
' As in this script we will not use all of them, only the ones we will use are included in this array

Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)

Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM " & NameOfTableInDB & " Order by " & ColunmNameIntable(0) & " Desc", DB

If RS.EOF And RS.BOF Then
Response.write "There are O records."
else
RS.MoveFirst
Response.write ("<form action=dbdelete.asp method=post>")
While Not RS.EOF

Response.write (DataName(1) & ": " & RS.Fields (ColunmNameIntable(1)) & "<BR>")

Response.write (DataName(2) & ": " & RS.Fields (ColunmNameIntable(2)) & "<BR>")

Response.write (DataName(3) & ": " & RS.Fields (ColunmNameIntable(3)) & "<BR>")

Response.write (DataName(4) & ": " & RS.Fields (ColunmNameIntable(4)) & "<BR>")

Response.write (DataName(5) & ": " & RS.Fields (ColunmNameIntable(5)) & "<BR>")

Response.write (DataName(6) & ": " & RS.Fields (ColunmNameIntable(6)) & "<BR>")


Response.write ("<input type=checkbox name=" & RS.Fields (ColunmNameIntable(0)) & " value=" & RS.Fields (ColunmNameIntable(0)) & ">Delete this record</a><BR>")
Response.write ("<HR>")
RS.MoveNext
Wend
Response.write ("<input type=submit value=Submit></form>")
End If

else
Response.write("<a href=dbcontrol.asp?1>Show list of records</a>")
end if
%>


dbaddrecord.asp

<%

DIM ColunmNameIntable(6)


ColunmNameIntable(0)="Id"
ColunmNameIntable(1)="Data1"
ColunmNameIntable(2)="Data2"
ColunmNameIntable(3)="Data3"
ColunmNameIntable(4)="Data4"
ColunmNameIntable(5)="Data5"
ColunmNameIntable(6)="Data6"

' When displaying data we will use this values
DIM DataName(6)

DataName(0)="Id"
DataName(1)="Role"
DataName(2)="Industry"
DataName(3)="Location"
DataName(4)="Salary"
DataName(5)="Status"
DataName(6)="More detail"

DIM PathToDatabase ' The location of our database within our server
PathToDatabase="D:\Domains\directionsatwork.co.uk\wwwroot\cgi-bin\DATABASE.mdb"

DIM NameOfTableInDB ' The name of the table in our Access database. In our case this name is "Table1"
NameOfTableInDB="Table1"

Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)

Set RS = Server.CreateObject("ADODB.Recordset")
RS.CursorType = 2
RS.LockType = 3
RS.Open "SELECT * FROM " & NameOfTableInDB, DB

RS.AddNew

RS (ColunmNameIntable(1)) = Request.form(ColunmNameIntable(1))

RS (ColunmNameIntable(2)) = Request.form(ColunmNameIntable(2))

RS (ColunmNameIntable(3)) = Request.form(ColunmNameIntable(3))

RS (ColunmNameIntable(4)) = Request.form(ColunmNameIntable(4))

RS (ColunmNameIntable(5)) = Request.form(ColunmNameIntable(5))

RS (ColunmNameIntable(6)) = Request.form(ColunmNameIntable(6))


RS.Update

Set RS = Nothing
Set DB = Nothing
Response.redirect("dbcontrol.asp")
%>


dbdelete.asp

<%

DIM PathToDatabase
PathToDatabase="D:\Domains\directionsatwork.co.uk\wwwroot\cgi-bin\DATABASE.mdb"

DIM NameOfTableInDB ' The name of the table in our Access database. In our case this name is "Table1"
NameOfTableInDB="Table1"

Set DB = Server.CreateObject("ADODB.Connection")
DB.mode = adModeReadWrite
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)
For Each x In Request.Form
DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id=" & x)
Next
Set RS = Nothing
Set DB = Nothing
Response.redirect("dbcontrol.asp")
%>

[russell]

try like this

Dim PathToDatabase
PathToDatabase="D:\Domains\directionsatwork.co.uk\wwwroot\cgi-bin\DATABASE.mdb"

Dim NameOfTableInDB ' The name of the table in our Access database. In our case this name is "Table1"
NameOfTableInDB="Table1"

Dim ar
Dim i
ar = Split(Request.Form("id"), ",")

Set DB = Server.CreateObject("ADODB.Connection")
DB.mode = adModeReadWrite
DB.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & PathToDatabase)

For i = 0 To ubound(ar)
DB.Execute("DELETE FROM " & NameOfTableInDB & " WHERE Id=" & clng(ar(i)))
Next

Set RS = Nothing
Set DB = Nothing
Response.redirect("dbcontrol.asp")

[jazzpiano]

Thanks for that - it doesn't return an errors when I try to delete something, but nothing actually gets deleted.....

Sorry to be a bother...

[russell]

how many check boxs checked?
in dbcontrol.asp, if u view source, what is in the html for the value of the checkboxes?

try changing db.execute("Delete...
to
Response.Write "Delete...
and comment out the redirect. this will show the query u r trying to execute. what does it show?

[jazzpiano]

I found the problem - the <FORM action=dbcaddrecord.asp method=post> was not set up for the deleterecord asp - DOH!

Sorry - thanks for your help everyone :-)