Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

ASP ODBC Database Errors

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
netimpact
Asked:
netimpact
  • 9
  • 7
1 Solution
 
neeraj523Commented:
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
 
netimpactAuthor Commented:
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
 
dosthCommented:
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!

 
netimpactAuthor Commented:
dosth:

I will try that and get back to you.
0
 
netimpactAuthor Commented:
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
 
dosthCommented:
you are upgrading to the filed to accept large data, you wont loss data :)
0
 
netimpactAuthor Commented:
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
 
dosthCommented:
thanks and give me points when all set
0
 
netimpactAuthor Commented:
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
 
dosthCommented:
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
 
netimpactAuthor Commented:
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
 
dosthCommented:
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
 
netimpactAuthor Commented:
Quick question where will I find the call WriteSQL() function. Will this reside in another module that is
linked to deletesite.asp?
0
 
dosthCommented:
in the top there will be some include files check in that
0
 
netimpactAuthor Commented:
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
 
netimpactAuthor Commented:
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
 
dosthCommented:
thanks for the points
0

Featured Post

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!

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now