Solved

Intermittent Error 2147467259 - Could not delete from specified tables.

Posted on 2006-07-07
10
488 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
Comment Utility
code?  something is wrong with it.

0
 

Author Comment

by:eknotts
Comment Utility
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
Comment Utility
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
 

Author Comment

by:eknotts
Comment Utility
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
Comment Utility
>> on error resume next

GET THAT OUT!  You cannot debug an application properly with that in your code.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 16

Expert Comment

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

Author Comment

by:eknotts
Comment Utility
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
Comment Utility
>>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
Comment Utility
>>I covers up all the problems

Typo: It covers up all the problems
0
 
LVL 6

Expert Comment

by:Dragonlaird
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now