Solved

Intermittent Error 2147467259 - Could not delete from specified tables.

Posted on 2006-07-07
10
495 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
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 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