Solved

ASP ODBC Database Errors

Posted on 2008-06-20
17
437 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
[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
  • 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
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!

 

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

Industry Leaders: 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
MS SQL and inserting dates into tables using stored procedures 6 39
Date on a table 16 34
PHP encrypted string and passing to a ASP Page 12 41
Boolean 13 20
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 informatio…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

756 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