Solved

Intermittent Error 2147467259 - Could not delete from specified tables.

Posted on 2006-07-07
10
497 Views
Last Modified: 2009-12-16
In an ASP page,  have a simple delete query on a Access DB. Selecting a record based on user selection from a table. Sometimes the delete query works and sometimes it returns the "Error 2147467259 - Could not delete from specified tables". Sometimes I can refresh the page displaying the error and then it may run successfully. Any ideas... I can provide the code if necessary.
0
Comment
Question by:eknotts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 31

Expert Comment

by:alorentz
ID: 17059564
code?  something is wrong with it.

0
 

Author Comment

by:eknotts
ID: 17060017
Here is the code:


<%
' My ASP program that selects the record based on the users selection (which).

myDSN="DSN=tnbdsndb;uid=Admin;pwd=''"

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
form_id=request.querystring("which")

sqltemp="select * from tbltest "
sqltemp=sqltemp & " where Account=" & form_id

set rstemp=conntemp.execute(sqltemp)


form_auid=rstemp("Account")
form_author=rstemp("Email")
form_year_born=rstemp("Company")
form_username=rstemp("Username")
form_uid=rstemp("UID")
form_pwd=rstemp("PWD")
form_date=rstemp("ProcessDate")


                              
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
%>
<form name="myauthor" action="authordeleteresponduser.asp" method="POST">

<input type="hidden" name="Account" value="<%=form_auid%>">

<p><font face="Verdana" size="2"><b>Account ID: </b></font> <%=form_auid%></p>

<p> <font face="Verdana" size="2"><b> Applicant Name:</b></font>
<input type="TEXT" name="Username" value="<%=form_username%>"></p>

<p> <font face="Verdana" size="2"><b> Email:</b></font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="TEXT" name="Email" value="<%=form_author%>"></p>

<p><font face="Verdana" size="2"><b> Company:</b></font> &nbsp;
<input type="TEXT" name="Company" value="<%=form_year_born%>"></p>

<p><font face="Verdana" size="2"><b> Company:</b></font> &nbsp;
<input type="TEXT" name="UID" value="<%=form_uid%>"></p>

<p><font face="Verdana" size="2"><b> PWD:</b></font> &nbsp;
<input type="TEXT" name="PWD" value="<%=form_pwd%>"></p>

<p><font face="Verdana" size="2"><b> Date:</b></font> &nbsp;
<input type="TEXT" name="ProcessDate" value="<%=form_date%>"></p>


<p> <input type="SUBMIT"> </p>
</form>


******************************** Theis is the (authordeleteresponduser.asp)


<% @language="vbscript" %>
<!--#include file="lib_errors.asp"-->
<HTML>
<head>
<TITLE>authordeleteresponduser.asp</TITLE>
<STYLE>
A {text-decoration: none;}
</STYLE>
</head>

<body bgcolor="#FFFFFF" link="#FFFFFF" vlink="#FFFF00" alink="#FFFF00">

<%
on error resume next
myDSN="DSN=tnbdsndb;uid=Admin;pwd=''"
form_id=request.form("Account")
form_username=request.form("Username")


mySQL="DELETE FROM tbltest "
mySQL=mySQL & " where Account = " & form_id

Set Conn = Server.CreateObject("ADODB.Connection")
conn.open myDSN

Conn.Execute mySQL,howmany


response.write "The statement " & mySQL & "<b> deleted " & howmany & " records</b><br>"
response.write "You have successfully DELETED " & form_username
Call ErrorVBScriptReport("Deleting...")
Call ErrorADOReport(mySQL,conn)

Conn.Close
set conn=nothing
%>


0
 
LVL 6

Expert Comment

by:John_Lennon
ID: 17060066
in your first page, you are not checking if the recordset have any value, maybe some times you don't get results, so when you try to pass values to authordeleteresponduser.asp you are not passing anything at all, try putting this lines in your first page

set rstemp=conntemp.execute(sqltemp)
if not rstemp.eof then

  form_auid=rstemp("Account")
  form_author=rstemp("Email")
  '...
  '...
  'all your proceess                        
else
  'No results found, try again
end if
rstemp.close

and in your second page try this

form_id=request.form("Account")
form_username=request.form("Username")
if form_id = "" and form_username = "" then
  response.write "Values missing"
  response.end
end if
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:eknotts
ID: 17062032
Problem still exists....I added your suggestion to both pages, although the first page always had values. Here is the revised second page:  

******************************** Theis is the (authordeleteresponduser.asp)

<% @language="vbscript" %>
<!--#include file="lib_errors.asp"-->
<HTML>
<head>
<TITLE>authordeleteresponduser.asp</TITLE>
<STYLE>
A {text-decoration: none;}
</STYLE>
</head>

<body bgcolor="#FFFFFF" link="#FFFFFF" vlink="#FFFF00" alink="#FFFF00">

<%
on error resume next
myDSN="DSN=tnbdsndb;uid=Admin;pwd=''"
form_id=request.form("Account")
form_username=request.form("Username")

if form_id = "" and form_username = "" then
  response.write "Values missing"
  response.end
end if

'mySQL = "DELETE FROM tbltest WHERE Account = " & form_id

mySQL="DELETE FROM tbltest "
mySQL=mySQL & " where Account = " & form_id

Set Conn = Server.CreateObject("ADODB.Connection")
conn.open myDSN

Conn.Execute mySQL,howmany
'Conn.Execute mySQL

response.write "The statement " & mySQL & "<b> deleted " & howmany & " records</b><br>"
'response.write "The elk statement " & mySQL & "<b> deleted " & " records</b><br>"
response.write "You have successfully DELETED " & form_username
Call ErrorVBScriptReport("Deleting...")
Call ErrorADOReport(mySQL,conn)

Conn.Close
set conn=nothing
%>

</BODY>
</HTML>
0
 
LVL 31

Accepted Solution

by:
alorentz earned 410 total points
ID: 17062088
>> on error resume next

GET THAT OUT!  You cannot debug an application properly with that in your code.
0
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17064573
Your Account field in the tbltest is numeric or string ?
0
 

Author Comment

by:eknotts
ID: 17064700
The Account field is numeric (Long Integer) becaause it is defined as an "AutoNumber" in the Access DB. But since I have iimplemented the changes suggested by alorentz and John_Lennon, I have not seen the problem again, just need to see if I can re-create the problem, before I go any further.

0
 
LVL 31

Expert Comment

by:alorentz
ID: 17064836
>>But since I have iimplemented the changes suggested by alorentz

Of course not...because On Error Resume Next is EVIL!  I covers up all the problems, if any, and then creates problems that don't exist.  NEVER put that in your code...or at least not until your absolutely positive it is production ready.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 17064841
>>I covers up all the problems

Typo: It covers up all the problems
0
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17067684
I suspect the reason he has On Error... in his code is because he is attempting to trap the error later in his code with the two subroutine calls ErrorVBScriptReport and ErrorADOReport however, when developing initial code, alorentz is quite right that you should not use the On Error.. as many of the useful info items are lost to any error routine and as he says, can often cause more problems since code continues to run despite the fact there is a problem - If this causes more problems later in the code, only the last problem is highlighted (e.g. the fact you couldn't delete a record from the DB) instead of the root cause (like perhaps the supplier value form_id is empty but the form_username isn't, or perhaps someone is trying to use an SQL Injection attack...?

You really need to keep track of the actual SQL that was generated when the error occured so we can see if your code is correctly formatting the SQL string. For example... imagine that I enter the following string into the form field called Account:

"0 Or Account Is Not Null"

Your code then runs...
===
form_id=request.form("Account")
===
And the resulting SQL is...
===
mySQL="DELETE FROM tbltest where Account = " & form_id
===
So you executethe following SQL against your database...
"DELETE FROM tbltest where Account = 0 Or Account Is Not Null"

Do you really want anyone to delete the entire contents of a table??? The above is a simple example, in reality, the results could be a lot more devastating.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP and Extracting XML 7 27
Use Mid in Html 6 22
ASP Error Handler 5 19
JQuery DataTable Functionality 8 15
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question