473,395 Members | 1,624 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > asp / active server pages > questions > how can i delete records from access

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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/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

10 2166
jhardman
3,406 Expert 2GB
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
Jared

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

Thanks Jim
Oct 5 '10 #3
jhardman
3,406 Expert 2GB
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
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
3,406 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. strSQL = "DELETE FROM Panel WHERE plantloc = '" & request.form("locvar") & "'"
Jared
Oct 7 '10 #6
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
3,406 Expert 2GB
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
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
3,406 Expert 2GB
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
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

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

Similar topics

6
Help! cannot delete records in the table "PRODUCTS"
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from...
Microsoft Access / VBA
2
Delete records in a query by referencing another
by: Robin | last post by:
I have a main table that I need to delete records that arn't referenced in another. Query says I cannot delete. If I remove the reference query all deletes ok. Hope there is a way around this ?...
Microsoft Access / VBA
2
Difficulty trying to delete records
by: Jim Devenish | last post by:
I have two tables: Deliveries and Invoices. An Invoice can relate to a number of Deliveries. The relevant fields are: Invoices: InvoiceID InvoiceDate Deliveries: DeliveryID
Microsoft Access / VBA
1
DoCmd to delete records
by: davea | last post by:
Hi I have a command button that when clicked deletes certain records from a subform based on an application number being equal. It deletes all records with the same applciation number as...
Microsoft Access / VBA
6
Faster Approach to delete records......
by: satish mullapudi | last post by:
Hi All, I am getting strange situation. These r the steps I have followed: 1. Created an EMPLOYEE table with around 14 fields & 688038 records. (so a large table indeed). 2. Tried to delete all...
DB2 Database
4
Need Help to delete records using 'recordset.Delete'
by: felicia | last post by:
Hi All, Below is my code to delete records: adodcAllEntries.Recordset.MoveFirst Do While (adodcAllEntries.Recordset.EOF = False) If adodcAllEntries.Recordset.Fields(0) = selected_id Then...
Visual Basic 4 / 5 / 6
2
Exposing Web Services methods for Add/Edit/Delete Records
by: =?Utf-8?B?SWJyYWhpbS4=?= | last post by:
Hello, I have a client c# application from which I want to Add/Edit/Delete & list records by connecting to Web Service. HOw can I write classes & methods in Web service projet that will : ...
.NET Framework
1
How to Delete records (Access/Excel)
by: elbatz | last post by:
Hi! Does anyone know the code of how to delete records in access using excel as reference. example: Delete * FROM table1 WHERE table1.ID=excelfile.ID ? Thanks
Visual Basic 4 / 5 / 6
9
Delete Records In Subform
by: MEGDZIA | last post by:
Could you please advise whether it is possible to delete records in suborm. When I highlight whole record it won't let me do it. I've chcecked all properties and it should allow to delete...
Microsoft Access / VBA
6
Delete records from main table if subtable is empty
by: kstevens | last post by:
I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete...
Microsoft Access / VBA
0
How to turn on java script in a villaon keypad mobile phone
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
Java
0
Batch import of multiple excel files into the database
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
How to build RAID in BIOS?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
Computer Hardware
0
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++
0
jinu1996
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Online Marketing
0
The easy way to turn off automatic updates for Windows 10/11
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
Windows Server

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!