How can I delete records from Access

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jimgunkel
    New Member
    • Jul 2010
    • 19
    #1

    How can I delete records from Access

    Error message

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E23)
    Row handle referred to a deleted row or a row marked for deletion.
    /paneldatatest/indiana/fdp/deletepaneldefa ult.asp, line 56

    Browser Type:
    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; MS-RTC LM 8; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)

    Page:
    POST 39 bytes to /paneldatatest/indiana/fdp/deletepaneldefa ult.asp

    POST Data:
    loc=1099&rem=00 9&bay=fdp009&ms =04&ps=24



    This is the code I have so far.

    Code:
    <% @language="vbscript" %>
<% Response.buffer = true %>

<html>
<head>
  <title>NE&P Panel Data</title>
</head>

<body background="\..\background.jpg">

<%

'************************************************************
'ADO is the keyword for google searches on help with DB stuff
'************************************************************

locvar = Request.QueryString("loc")

locCount = 1




Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 


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


strSQL = "SELECT panel.PlantLoc, panel.panelconnection, panel.panelPosition FROM Panel;"   'Set up a SQL command in a string variable

rsDeleteComments.CursorType = 2

rsDeleteComments.LockType = 3

rsDeleteComments.Open strSQL, MyConn

locBay = Request.Form("bay")

locBay = Ucase(locBay)

locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount

locSize = Request.Form("ps")

locSize = Cint(locSize)

do Until locCount > locSize


'if not rsDeleteComments.duplicate then
'   do while not rsAddComments.duplicate

rsDeleteComments.Delete
rsDeleteComments.Fields("PlantLoc")= locvar
'rsDeleteComments.Fields("panelconnection")= Request.Form("ps") & " " & Request.Form("ct")
'rsDeleteComments.Fields("panelPosition")=locCount

'   rsDeleteComments.movenext
' loop
'else
'   response.write "<h1> ERROR - Panel does not exist </h1>"
'end if

rsDeleteComments.Update

locCount = (locCount + 1)

if locCount < 10 then
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  else 
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
end if

loop

rsDeleteComments.Close

        Set rsDeleteComments = Nothing

MyConn.close  ' close the database connection

%>
    Tags: None
    • jhardman
      Recognized Expert Specialist
      • Jan 2007
      • 3405
      #2
      ok, I can see why there is a problem, but I can't see what you are trying to do. What are you trying to do, delete the row, or update it?

      Jared

        Comment

        • Jimgunkel
          New Member
          • Jul 2010
          • 19
          #3
          Jared

          I am tring to delete 12 or more rows from my Access Database.

          Thanks Jim

            Comment

            • jhardman
              Recognized Expert Specialist
              • Jan 2007
              • 3405
              #4
              let's walk through this then.

              Line 55 you say
              Code:
              rsDeleteComments.Delete
              this should delete the current line of the recordset.

              Line 56 you say
              Code:
              rsDeleteComments.Fields("PlantLoc")= locvar
              which sets the field "PlantLoc" of the current row to the value of the "locvar" variable. this is causing an error, because in line 55 you just deleted the entire row.

              Line 66 you say
              Code:
              rsDeleteComments.Update
              , this communicates back to the database to sync with the in-memory recordset, but you of course are getting an error before that point, so this has probably never been done.

              Line 31 you make a "select" sql statement. Does this just pull up all of the records in the table, but you want to delete some, not all of them, right? If you could figure out an exact where clause that pulls up the exact records you want to delete, you could just change it to a "delete" statement instead of a "select" statement:
              Code:
              "DELETE FROM Panel WHERE plantloc = '" & request.form("loc") & "'"
              Then you wouldn't have to go through all the rigmarole of pulling up a recordset and going through it line by line.

              Let me know if this helps.

              Jared

                Comment

                • Jimgunkel
                  New Member
                  • Jul 2010
                  • 19
                  #5
                  I'm getting a new error message:

                  Technical Information (for support personnel)

                  Error Type:
                  Microsoft VBScript compilation (0x800A0400)
                  Expected statement
                  /paneldatatest/indiana/fdp/deletepaneldefa ult.asp, line 49


                  Browser Type:
                  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; MS-RTC LM 8; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)

                  Page:
                  POST 39 bytes to /paneldatatest/indiana/fdp/deletepaneldefa ult.asp

                  POST Data:
                  loc=1099&rem=00 9&bay=FDP009&ms =04&ps=24

                  Code:
                  <% @language="vbscript" %>
<% Response.buffer = true %>

<html>
<head>
  <title>NC NE&P Panel Data</title>
</head>

<body background="\..\background.jpg">

<%

'************************************************************
'ADO is the keyword for google searches on help with DB stuff
'************************************************************

locvar = Request.QueryString("loc")

locCount = 1


Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 


'         Set rsDeleteComments = Server.CreateObject("ADODB.Recordset")


'strSQL = "SELECT panel.PlantLoc, panel.panelconnection, panel.panelPosition FROM Panel;"   'Set up a SQL command in a string variable

rsDeleteComments.CursorType = 2

rsDeleteComments.LockType = 3

rsDeleteComments.Open strSQL, MyConn

locBay = Request.Form("bay")

locBay = Ucase(locBay)

locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount

locSize = Request.Form("ps")

locSize = Cint(locSize)

do Until locCount > locSize

"DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"

'rsDeleteComments.Delete
'rsDeleteComments.Fields("PlantLoc")= locvar


locCount = (locCount + 1)

if locCount < 10 then
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  else 
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
end if

loop


MyConn.close  ' close the database connection

%>

                    Comment

                    • jhardman
                      Recognized Expert Specialist
                      • Jan 2007
                      • 3405
                      #6
                      Code:
                      strSQL = "DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"
                      Jared

                        Comment

                        • Jimgunkel
                          New Member
                          • Jul 2010
                          • 19
                          #7
                          Will this command work with MS Access. I tried it - I no longer get an error message but the data was not deleted.

                          Code:
                          locvar = Request.QueryString("loc")

locCount = 1


Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 


locBay = Request.Form("bay")

locBay = Ucase(locBay)

locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount

locSize = Request.Form("ps")

locSize = Cint(locSize)

do Until locCount > locSize


strSQL="DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"


locCount = (locCount + 1)

if locCount < 10 then
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  else 
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
end if

loop


MyConn.close  ' close the database connection

%>

                            Comment

                            • jhardman
                              Recognized Expert Specialist
                              • Jan 2007
                              • 3405
                              #8
                              ahh yes. You connected to the db but never actually executed the command. try this after yiou set the value of strSQL
                              Code:
                                 set objCommand = server.createobject("adodb.command")
   objCommand.activeconnection = myConn
   objCommand.commandtext = strSQL
   objCommand.execute
   objCommand.close
                              Jared

                                Comment

                                • Jimgunkel
                                  New Member
                                  • Jul 2010
                                  • 19
                                  #9
                                  New error message:
                                  Technical Information (for support personnel)

                                  Error Type:
                                  Microsoft VBScript runtime (0x800A01B6)
                                  Object doesn't support this property or method: 'close'
                                  /paneldatatest/indiana/fdp/deletepaneldefa ult.asp, line 45


                                  Browser Type:
                                  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; MS-RTC LM 8; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)

                                  Page:
                                  POST 39 bytes to /paneldatatest/indiana/fdp/deletepaneldefa ult.asp

                                  POST Data:
                                  loc=1099&rem=00 9&bay=fdp009&ms =01&ps=24

                                  Code:
                                  <% @language="vbscript" %>
<% Response.buffer = true %>

<html>
<head>
  <title> NC NE&P Panel Data</title>
</head>

<body background="\..\background.jpg">

<%

'************************************************************
'ADO is the keyword for google searches on help with DB stuff
'************************************************************

locvar = Request.QueryString("loc")

locCount = 1


Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 


locBay = Request.Form("bay")

locBay = Ucase(locBay)

locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount

locSize = Request.Form("ps")

locSize = Cint(locSize)

do Until locCount > locSize


strSQL="DELETE FROM Panel WHERE plantloc = '" & locvar & "'"

   set objCommand = server.createobject("adodb.command") 
   objCommand.activeconnection = myConn 
   objCommand.commandtext = strSQL 
   objCommand.execute 
   objCommand.close 

response.write locvar

locCount = (locCount + 1)

if locCount < 10 then
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  else 
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
end if



loop


MyConn.close  ' close the database connection

%>
                                  Last edited by jhardman; Oct 11 '10, 09:27 PM. Reason: fixed code tags. Thanks for trying! and please continue to use them in the future

                                    Comment

                                    • jhardman
                                      Recognized Expert Specialist
                                      • Jan 2007
                                      • 3405
                                      #10
                                      hmm. It looks like you don't need to close the objCommand object. try to comment out that line. If that works, then change it to
                                      Code:
                                      set objCommand = nothing
                                      for best practises.

                                      Jared

                                        Comment

                                        • Jimgunkel
                                          New Member
                                          • Jul 2010
                                          • 19
                                          #11
                                          I changed the code in line 37 and the web page is deleting records.

                                          Thanks!

                                          Code:
                                          <% @language="vbscript" %>
<% Response.buffer = true %>

<html>
<head>
  <title> NC NE&P Panel Data</title>
</head>

<body background="\..\background.jpg">

<%

'************************************************************
'ADO is the keyword for google searches on help with DB stuff
'************************************************************

locvar = Request.QueryString("loc")

locCount = 1


Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldatatest\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"

locBay = Request.Form("bay")

locBay = Ucase(locBay)

Sqlst = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable

locSize = Request.Form("ps")

locSize = Cint(locSize)

do Until locCount > locSize

strSQL ="DELETE FROM Panel WHERE plantloc = '" & locvar & "'"

   set objCommand = server.createobject("adodb.command") 
   objCommand.activeconnection = myConn 
   objCommand.commandtext = strSQL 
   objCommand.execute 
   set objCommand = nothing



response.write locvar

locCount = (locCount + 1)

if locCount < 10 then
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  else 
   locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
end if



loop


MyConn.close  ' close the database connection

%>

                                            Comment

                                            Previous template Next
                                            Working...