www.webdeveloper.com
Results 1 to 9 of 9

Thread: Delete entries on Access database with ASP

  1. #1
    Join Date
    Dec 2006
    Posts
    10

    Delete entries on Access database with ASP

    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

  2. #2
    Join Date
    Jun 2005
    Location
    Winston-Salem, NC
    Posts
    318
    Try changing this line:
    Code:
    DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id=" & x)
    To this:
    Code:
    DB.Execute("DELETE * FROM " & NameOfTableInDB & " WHERE Id='" & x & "'")

  3. #3
    Join Date
    Dec 2006
    Posts
    10
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    2,745
    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
    Code:
    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.


  5. #5
    Join Date
    Dec 2006
    Posts
    10
    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")
    %>

  6. #6
    Join Date
    Feb 2003
    Posts
    2,745
    try like this
    Code:
    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")

  7. #7
    Join Date
    Dec 2006
    Posts
    10
    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...

  8. #8
    Join Date
    Feb 2003
    Posts
    2,745
    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?

  9. #9
    Join Date
    Dec 2006
    Posts
    10
    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 :-)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles