By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,162 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,162 IT Pros & Developers. It's quick & easy.

How can I delete records from Access

P: 19
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/deletepaneldefault.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/deletepaneldefault.asp

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



This is the code I have so far.

Expand|Select|Wrap|Line Numbers
  1. <% @language="vbscript" %>
  2. <% Response.buffer = true %>
  3.  
  4. <html>
  5. <head>
  6.   <title>NE&P Panel Data</title>
  7. </head>
  8.  
  9. <body background="\..\background.jpg">
  10.  
  11. <%
  12.  
  13. '************************************************************
  14. 'ADO is the keyword for google searches on help with DB stuff
  15. '************************************************************
  16.  
  17. locvar = Request.QueryString("loc")
  18.  
  19. locCount = 1
  20.  
  21.  
  22.  
  23.  
  24. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  25. MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 
  26.  
  27.  
  28.          Set rsDeleteComments = Server.CreateObject("ADODB.Recordset")
  29.  
  30.  
  31. strSQL = "SELECT panel.PlantLoc, panel.panelconnection, panel.panelPosition FROM Panel;"   'Set up a SQL command in a string variable
  32.  
  33. rsDeleteComments.CursorType = 2
  34.  
  35. rsDeleteComments.LockType = 3
  36.  
  37. rsDeleteComments.Open strSQL, MyConn
  38.  
  39. locBay = Request.Form("bay")
  40.  
  41. locBay = Ucase(locBay)
  42.  
  43. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  44.  
  45. locSize = Request.Form("ps")
  46.  
  47. locSize = Cint(locSize)
  48.  
  49. do Until locCount > locSize
  50.  
  51.  
  52. 'if not rsDeleteComments.duplicate then
  53. '   do while not rsAddComments.duplicate
  54.  
  55. rsDeleteComments.Delete
  56. rsDeleteComments.Fields("PlantLoc")= locvar
  57. 'rsDeleteComments.Fields("panelconnection")= Request.Form("ps") & " " & Request.Form("ct")
  58. 'rsDeleteComments.Fields("panelPosition")=locCount
  59.  
  60. '   rsDeleteComments.movenext
  61. ' loop
  62. 'else
  63. '   response.write "<h1> ERROR - Panel does not exist </h1>"
  64. 'end if
  65.  
  66. rsDeleteComments.Update
  67.  
  68. locCount = (locCount + 1)
  69.  
  70. if locCount < 10 then
  71.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  72.   else 
  73.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
  74. end if
  75.  
  76. loop
  77.  
  78. rsDeleteComments.Close
  79.  
  80.         Set rsDeleteComments = Nothing
  81.  
  82. MyConn.close  ' close the database connection
  83.  
  84. %>
  85.  
Oct 4 '10 #1

✓ answered by jhardman

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
Expand|Select|Wrap|Line Numbers
  1. set objCommand = nothing
for best practises.

Jared

Share this Question
Share on Google+
10 Replies


jhardman
Expert 2.5K+
P: 3,405
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
Oct 4 '10 #2

P: 19
Jared

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

Thanks Jim
Oct 5 '10 #3

jhardman
Expert 2.5K+
P: 3,405
let's walk through this then.

Line 55 you say
Expand|Select|Wrap|Line Numbers
  1. rsDeleteComments.Delete
this should delete the current line of the recordset.

Line 56 you say
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. "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
Oct 6 '10 #4

P: 19
I'm getting a new error message:

Technical Information (for support personnel)

Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/paneldatatest/indiana/fdp/deletepaneldefault.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/deletepaneldefault.asp

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

Expand|Select|Wrap|Line Numbers
  1. <% @language="vbscript" %>
  2. <% Response.buffer = true %>
  3.  
  4. <html>
  5. <head>
  6.   <title>NC NE&P Panel Data</title>
  7. </head>
  8.  
  9. <body background="\..\background.jpg">
  10.  
  11. <%
  12.  
  13. '************************************************************
  14. 'ADO is the keyword for google searches on help with DB stuff
  15. '************************************************************
  16.  
  17. locvar = Request.QueryString("loc")
  18.  
  19. locCount = 1
  20.  
  21.  
  22. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  23. MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 
  24.  
  25.  
  26. '         Set rsDeleteComments = Server.CreateObject("ADODB.Recordset")
  27.  
  28.  
  29. 'strSQL = "SELECT panel.PlantLoc, panel.panelconnection, panel.panelPosition FROM Panel;"   'Set up a SQL command in a string variable
  30.  
  31. rsDeleteComments.CursorType = 2
  32.  
  33. rsDeleteComments.LockType = 3
  34.  
  35. rsDeleteComments.Open strSQL, MyConn
  36.  
  37. locBay = Request.Form("bay")
  38.  
  39. locBay = Ucase(locBay)
  40.  
  41. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  42.  
  43. locSize = Request.Form("ps")
  44.  
  45. locSize = Cint(locSize)
  46.  
  47. do Until locCount > locSize
  48.  
  49. "DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"
  50.  
  51. 'rsDeleteComments.Delete
  52. 'rsDeleteComments.Fields("PlantLoc")= locvar
  53.  
  54.  
  55. locCount = (locCount + 1)
  56.  
  57. if locCount < 10 then
  58.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  59.   else 
  60.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
  61. end if
  62.  
  63. loop
  64.  
  65.  
  66. MyConn.close  ' close the database connection
  67.  
  68. %>
  69.  
Oct 7 '10 #5

jhardman
Expert 2.5K+
P: 3,405
Expand|Select|Wrap|Line Numbers
  1. strSQL = "DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"
Jared
Oct 7 '10 #6

P: 19
Will this command work with MS Access. I tried it - I no longer get an error message but the data was not deleted.

Expand|Select|Wrap|Line Numbers
  1. locvar = Request.QueryString("loc")
  2.  
  3. locCount = 1
  4.  
  5.  
  6. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  7. MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 
  8.  
  9.  
  10. locBay = Request.Form("bay")
  11.  
  12. locBay = Ucase(locBay)
  13.  
  14. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  15.  
  16. locSize = Request.Form("ps")
  17.  
  18. locSize = Cint(locSize)
  19.  
  20. do Until locCount > locSize
  21.  
  22.  
  23. strSQL="DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"
  24.  
  25.  
  26. locCount = (locCount + 1)
  27.  
  28. if locCount < 10 then
  29.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  30.   else 
  31.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
  32. end if
  33.  
  34. loop
  35.  
  36.  
  37. MyConn.close  ' close the database connection
  38.  
  39. %>
  40.  
Oct 8 '10 #7

jhardman
Expert 2.5K+
P: 3,405
ahh yes. You connected to the db but never actually executed the command. try this after yiou set the value of strSQL
Expand|Select|Wrap|Line Numbers
  1.    set objCommand = server.createobject("adodb.command")
  2.    objCommand.activeconnection = myConn
  3.    objCommand.commandtext = strSQL
  4.    objCommand.execute
  5.    objCommand.close
Jared
Oct 8 '10 #8

P: 19
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/deletepaneldefault.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/deletepaneldefault.asp

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

Expand|Select|Wrap|Line Numbers
  1. <% @language="vbscript" %>
  2. <% Response.buffer = true %>
  3.  
  4. <html>
  5. <head>
  6.   <title> NC NE&P Panel Data</title>
  7. </head>
  8.  
  9. <body background="\..\background.jpg">
  10.  
  11. <%
  12.  
  13. '************************************************************
  14. 'ADO is the keyword for google searches on help with DB stuff
  15. '************************************************************
  16.  
  17. locvar = Request.QueryString("loc")
  18.  
  19. locCount = 1
  20.  
  21.  
  22. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  23. MyConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Inetpub\wwwroot\paneldatatest\databases\FDPIN.mdb;Uid=;Pwd=niemtel;") 
  24.  
  25.  
  26. locBay = Request.Form("bay")
  27.  
  28. locBay = Ucase(locBay)
  29.  
  30. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  31.  
  32. locSize = Request.Form("ps")
  33.  
  34. locSize = Cint(locSize)
  35.  
  36. do Until locCount > locSize
  37.  
  38.  
  39. strSQL="DELETE FROM Panel WHERE plantloc = '" & locvar & "'"
  40.  
  41.    set objCommand = server.createobject("adodb.command") 
  42.    objCommand.activeconnection = myConn 
  43.    objCommand.commandtext = strSQL 
  44.    objCommand.execute 
  45.    objCommand.close 
  46.  
  47. response.write locvar
  48.  
  49. locCount = (locCount + 1)
  50.  
  51. if locCount < 10 then
  52.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  53.   else 
  54.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
  55. end if
  56.  
  57.  
  58.  
  59. loop
  60.  
  61.  
  62. MyConn.close  ' close the database connection
  63.  
  64. %>
  65.  
Oct 11 '10 #9

jhardman
Expert 2.5K+
P: 3,405
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
Expand|Select|Wrap|Line Numbers
  1. set objCommand = nothing
for best practises.

Jared
Oct 11 '10 #10

P: 19
I changed the code in line 37 and the web page is deleting records.

Thanks!

Expand|Select|Wrap|Line Numbers
  1. <% @language="vbscript" %>
  2. <% Response.buffer = true %>
  3.  
  4. <html>
  5. <head>
  6.   <title> NC NE&P Panel Data</title>
  7. </head>
  8.  
  9. <body background="\..\background.jpg">
  10.  
  11. <%
  12.  
  13. '************************************************************
  14. 'ADO is the keyword for google searches on help with DB stuff
  15. '************************************************************
  16.  
  17. locvar = Request.QueryString("loc")
  18.  
  19. locCount = 1
  20.  
  21.  
  22. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  23. MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldatatest\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
  24.  
  25. locBay = Request.Form("bay")
  26.  
  27. locBay = Ucase(locBay)
  28.  
  29. Sqlst = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
  30.  
  31. locSize = Request.Form("ps")
  32.  
  33. locSize = Cint(locSize)
  34.  
  35. do Until locCount > locSize
  36.  
  37. strSQL ="DELETE FROM Panel WHERE plantloc = '" & locvar & "'"
  38.  
  39.    set objCommand = server.createobject("adodb.command") 
  40.    objCommand.activeconnection = myConn 
  41.    objCommand.commandtext = strSQL 
  42.    objCommand.execute 
  43.    set objCommand = nothing
  44.  
  45.  
  46.  
  47. response.write locvar
  48.  
  49. locCount = (locCount + 1)
  50.  
  51. if locCount < 10 then
  52.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  53.   else 
  54.    locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-" & locCount
  55. end if
  56.  
  57.  
  58.  
  59. loop
  60.  
  61.  
  62. MyConn.close  ' close the database connection
  63.  
  64. %>
  65.  
Oct 12 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.