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
Solved

ASP ODBC Database Errors

Posted on 2008-06-20
17
436 Views
Last Modified: 2012-06-27
Hi we have inherited this ASP/IIS code which seems to be failing with database errors.
This code deletes and removes websites from our IIS 5 Server on Windows 2000.
it also has data on SQL Server 2000 which it updates.

Please see below for more details.

One of the ERRORs can be found immediatley below:
=================================================
Removed user account error '80020009'

/Deletesite/deletesite.asp, line 111


And the other ERROR can be found below:
=================================================
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/Deletesite/deletesite.asp, line 205


Can you put a finger on this and help with a possible fix/solution?
I have pasted a sample of the cod below.

<%@ Language=VBScript %> <!-- #include virtual="\includes\includes.asp" -->
<html>
	<base target="main">
	<head>
		<link rel="stylesheet" type="text/css" href="/stylesheets/styles.css">
			<base target="_top">
			<title>Site Deletion</title>
	</head>
	<body bgcolor="#FFFFFF" text="#000000" marginwidth="0" marginheight="0" leftmargin="0"
		topmargin="0">
		<table width="918" cellspacing="0" cellpadding="0" border="0" height="605">
			<tr>
				<td width="365" background="../images/background2.jpg" height="579" valign="top">&nbsp;</td>
				<td width="220" height="579" valign="top"></td>
				<td width="1306" height="579" valign="top">&nbsp;
					<p align="left">&nbsp;
					</p>
					<h1 align="left"><b><font color="#660033" size="4" face="Arial">Site Deletion</font></b></h1>
					<p><b><font color="#808080">Enter Domain name with the www.</font></b>
					</p>
					<p><b><font color="#808080">This will 'permanently' remove the User, website, FrontPage 
								Extensions or FTP.</font></b></p>
					<p>&nbsp;
					</p>
					<p>&nbsp;</p>
					<form action="deletesite.asp?method=process" method="POST" target="main" name="FrontPage_Form1">
						<table border="0" cellpadding="0" cellspacing="0" width="402" height="67">
							<tr>
								<td width="189" align="right" height="25"><b><font color="#660033">Domain :&nbsp;</font></b></td>
								<td width="209" height="25"><b> <input type="text" name="txtDomain" size="20" value="www."></b></td>
							</tr>
							<tr>
								<td width="189" align="right" height="15"><b><font color="#660033">Reason for Deletion :&nbsp;&nbsp; </font></b></td>
								<td width="209" height="15"><b> <input type="text" name="txtReason" size="25"></b></td>
							</tr>
							<tr>
								<td width="189" align="right" height="15"><b><font color="#660033">Remove Content :&nbsp;&nbsp; </font></b></td>
								<td width="209" height="15"><b> <input type="checkbox" name="txtYN" value="on"></b></td>
							</tr>
<!--							<tr>
								<td width="189" align="right" height="15"><b><font color="#660033">Ignore DB Cleck :&nbsp;&nbsp; </font></b></td>
								<td width="209" height="15"><b> <input type="checkbox" name="txtDB" value="on" ID="Checkbox1"></b></td>
							</tr> -->
							<tr>
								<td width="189" align="right" height="15"></td>
								<td width="209" height="15"></td>
							</tr>
							<tr>
								<td width="189" align="right" height="27"></td>
								<td width="209" height="27"><b><input type="submit" value="Delete Site" name="Delete"></b></td>
							</tr>
						</table>
					</form>
					<%
on error resume next
errors = 0
method = request.querystring("method")
if method = "process" then
	strDomain = request.form("txtDomain")
		if instr (1, strdomain, "%") <> 0 then
			response.redirect("..\error.asp?method=wildcard")
		end if
	strReason = Request.form("txtReason")
	set oConn = createobject("ADODB.Connection")
	oConn.open "Corp_sql", "Corp_dsn", "support"
' --------------------------------------------------------
' ----- Check domain exists in the database
' --------------------------------------------------------
	strSQL = "SELECT * FROM custdata WHERE domain = '" & strDomain & "'"
	set adoRS = oConn.Execute(strSQL)
' Removed section below (and an end if at the end) to make it easier to clear up badly created sites
'	if adoRS.EOF then
'		response.redirect ("../error.asp?method=nosite")
'	Else
' ------------------------------------------------------
' ----- Remove user from commercial domain
' ----- remove all objects in the ou
' ------------------------------------------------------
set objuser = getobject("LDAP://OU=" & adoRS("Domain") & ",OU=MyOU,DC=MyDC,DC=MyCompany,DC=Corp")
for each item in objuser
	objuser.delete item.class, item.name
next
objUser.delete
' ----- then remove the OU
set objDomain = getobject("LDAP://OU=MyOU,DC=MyDC,DC=MyCompany,DC=Corp")
objDomain.delete "organizationalUnit", "ou=" & adoRS("Domain")
response.write("<br> Removed OU " & adoRS("Domain"))
set rootOBJ = nothing
set UserOBJ = nothing
response.Write("<br> Removed user account")
call writesql(adoRs("domain"), "Success", "Removed user account")
err.clear
' -------------------------------------------------------
' ----- Check for frontpage or FTP
' ----- And remove the relevant one
' ----- This bit works - commenting it out for now
' -------------------------------------------------------
		if adoRS("FrontpageExt") = "True" or adoRS("FrontpageExt") = 1 then
			call FPmodify("Uninstall", "na", adoRS("instance"))
			call writeSQL(adoRS("Domain"), "Success", "Removed Frontpage Extensions")
			else
			call FTPmodify("Uninstall", adoRS("username"), "null" )
			call writeSQL(adoRS("Domain"), "Success", "Removed FTP Site")
		end if
' -------------------------------------------------------
' ----- remove Database entries for all sub-tables
' -------------------------------------------------------
on error goto 0
set dConn = createobject("ADODB.Connection")
dConn.open "The_sql", "The_dsn", "support"
strSql = "DELETE FROM sub_domain WHERE master='" & adoRS("Domain") & "'"
dConn.Execute(strSQL)
Response.Write("<br> Deleted Sub-domains from database")
 
strSql = "DELETE FROM table_dsn WHERE domain='" & adoRS("Domain") & "'"
dConn.Execute(strSQL)
Response.Write("<br> Deleted DSN's from database")
 
strSql = "DELETE FROM table_permissions WHERE domain='" & adoRS("Domain") & "'"
dConn.Execute(strSQL)
Response.Write("<br> Deleted Directory Permissions from database")
 
strSql = "DELETE FROM table_users WHERE domain='" & adoRS("Domain") & "'"
dConn.Execute(strSQL)
Response.Write("<br> Deleted Sub-Users from database")
 
strSql = "DELETE FROM table_web WHERE Master='" & adoRS("Domain") & "'"
dConn.Execute(strSQL)
 
Response.Write("<br> Deleted Sub-webs from database")
if err <> 0 then 
	response.Write("<br><b>Error tidying up databases</b>")
else
	Response.Write("<br><b>Tidied up Databsaes OK</b>")
end if
err.Clear
' -------------------------------------------------------
' ----- Delete Site from IIS
' -------------------------------------------------------
	Set webOBJ = GetObject("IIS://localhost/W3SVC")
' ----- To get the web server id - query the db (adoRS("instance")
	'Connect to website instance to obtain information
	Set vRoot = GetObject("IIS://localhost/W3SVC/" & adoRS("instance") & "/root")
	strPath = vroot.path
'	Response.Write "IIS://localhost/W3SVC/" & webserverID & "<br>"
' ----- Remove the /root section
	Set vRoot = GetObject("IIS://localhost/W3SVC")
	Call vRoot.Delete("IIsWebVirtualDir", adoRS("instance"))
	if err <> 0 then
		Response.write("<br> Error removing web site instance - maybe it was never there")
		errors = errors + 1
	else
		Call writeSQL (adoRS("domain"), "Success", "Remove Web Site Instance")
	end if
	err.clear
' --------------------------------------------------
' ----- Copy web site filder  to the 'deleted' folder
' ----- Then delete it
' --------------------------------------------------
	on error resume next
	if request.Form("txtYN") = "on" then
		set objFSO=createobject("scripting.filesystemobject")
		response.write "<br> "& strPath
		objfso.CopyFolder strPath, "..\deletedsites\", OverwriteFiles
		if err <> 0 then
			response.write("<br> Error removing directory from staging server - Maybe it was empty / missing?")
			errors = errors + 1
		else
			objFSO.DeleteFolder strPath, Force
			response.Write("<br>Successfully copied directory to DeletedSites and removed original")
		end if
		err.clear
	else
		Response.Write("<br><b>Content to be kept, not moving it</b>")
	end if
	on error goto 0
' --------------------------------------------------
' ----- Remove the entry from the main database 
' ----- Write to the "Deleted sites" table
' ----- then use writeSQL one last time
' ----- and redirect to the completion page
' --------------------------------------------------
	updateSQL = "DELETE FROM CustomerData WHERE domain = '" & adoRS("domain") & "'"
	response.write("<br> " & updateSQL)
	set nConn = createobject("ADODB.Connection")
	nConn.open "The_sql", "The_dsn", "support"
	set adoUpdate = nConn.execute(updateSQL)
		if err <> 0 then
			Response.write ("<br> Somerandom error has occured here " & err.description )
		else
			Response.Write("<br> Removed user from 'CustomerData' database")
		end if
	nConn.close
	err.clear
	set updateSQl = nothing
	set mConn = Createobject("ADODB.Connection")
	mConn.open "The_SQL", "The_dsn", "Support"
	Set addRS = createobject("ADODB.Recordset")
	addRS.CursorType=3
	addRS.LockType=3
	addRS.open "deletedsites", mConn
	addRS.addnew
	addRS.fields("Domain") = adoRS("domain")
	addRS.Fields("datetime") = now()
	addRS.fields("reason") = request.form("txtReason")
	addRS.fields("deletedby") = request.servervariables("Logon_User")
	addRS.update
	call WriteSQL (adoRS("domain"), "Success", "Completed Removing Seb Site")
		if err <> 0 then
			Response.write("<br> Error writing to the 'Deleted' Database " & err.number & " : " & err.description)
		else
			Response.write("<br> Written to the 'Deleted' Database Successfully")
		end if
	If errors = 0 then
' -----------------------------------------------
' ----- redirect to node1 and delete the site / directory from there
' -----------------------------------------------
		response.redirect("../complete.asp?method=deleted&Domain=" & strDomain)
	end if
	Response.write("<br> Site deleted successfully.")
' ----- This is the end
'	end if
'oConn.close
end if
%>
				</td>
				<td width="904" height="579" valign="top">
					<img border="0" src="../graphics/logo120.gif"></td>
			</tr>
		</table>
	</body>
</html>

Open in new window

0
Comment
Question by:netimpact
  • 9
  • 7
17 Comments
 
LVL 22

Expert Comment

by:neeraj523
ID: 21829730
Can you mentione the line number 111 and 205 ??

Also comment On error resume Next through out your code and then see the error msgs..
0
 

Author Comment

by:netimpact
ID: 21829754
Line 111
===========
strSql = "DELETE FROM sub_domain WHERE master='" & adoRS("Domain") & "'"
dConn.Execute(strSQL)

Line 205
===========
addRS.Fields("datetime") = now()
addRS.fields("reason") = request.form("txtReason")

0
 
LVL 15

Accepted Solution

by:
dosth earned 500 total points
ID: 21829772
adoRS("Domain") instead of using this on 111 assing this to a variable

add like this after line 90

strDomain = adoRS("Domain")

and change your statement to use this Variable


int the database addRS.fields("reason") = request.form("txtReason")

please change the data type of this filed to TEXT data type.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:netimpact
ID: 21830903
dosth:

I will try that and get back to you.
0
 

Author Comment

by:netimpact
ID: 21831116
dosth:
The datatype is currently nVarChar. If I attempt to change the datatype to TEXT it prompts me that I will loss the data. what should I do to save teh data and change the type. Any ideas? Thanks
0
 
LVL 15

Expert Comment

by:dosth
ID: 21831141
you are upgrading to the filed to accept large data, you wont loss data :)
0
 

Author Comment

by:netimpact
ID: 21833228
Code seem to be holding it so far I only got one more error. We do more tests and get back to you. Thanks.
0
 
LVL 15

Expert Comment

by:dosth
ID: 21833524
thanks and give me points when all set
0
 

Author Comment

by:netimpact
ID: 21839247
One quick question just out of interest, what is the reason for using TEXT. I thought it is much larger and takes BLOB as well, which makes it muck slower? Thanks
0
 
LVL 15

Expert Comment

by:dosth
ID: 21843817
if you dont want to change to TEXT data type then change to varchar(255) and restrict the user to enter only 255 chars
0
 

Author Comment

by:netimpact
ID: 21845177
Hi dosth:

After the code update we get tis latest error below:

error '80070094'
/Deletesite/deletesite.asp, line 148
Erroring this line ->       Call vRoot.Delete("IIsWebVirtualDir", adoRS("instance"))
0
 
LVL 15

Expert Comment

by:dosth
ID: 21845332
what this function doing?

try like this

strInstance = trim(adoRS("instance"))

Response.Write(strInstance)

see the values comes correct

Call vRoot.Delete("IIsWebVirtualDir", strInstance)
0
 

Author Comment

by:netimpact
ID: 21845482
Quick question where will I find the call WriteSQL() function. Will this reside in another module that is
linked to deletesite.asp?
0
 
LVL 15

Expert Comment

by:dosth
ID: 21845761
in the top there will be some include files check in that
0
 

Author Comment

by:netimpact
ID: 21846025
I am still getting the same error '80070094' on the same line
/Deletesite/deletesite.asp, line 148

This only happens afeter a few deletes of the site. presumably on the third delete. Does the error tell us anything?
0
 

Author Comment

by:netimpact
ID: 21846187
As a matter of fact if error '80070094' happens it leaves a lot of fragments behind now which I have to go and cleanup manually. That is even worst than when I started troubleshooting this.
0
 
LVL 15

Expert Comment

by:dosth
ID: 21972171
thanks for the points
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Lync server 2013 or Skype for business Backup Service Error ID 4049 – After File Share Migration
A phishing scam that claims a recipient’s credit card details have been “suspended” is the latest trend in spoof emails.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

839 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