Solved

ASP ODBC Database Errors

Posted on 2008-06-20
17
422 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
 

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
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

 

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

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.

Join & Write a Comment

Suggested Solutions

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

24 Experts available now in Live!

Get 1:1 Help Now