Object Required 1

[A1Pat]

I'm having trouble understand why this ERROR keep happening:
Microsoft VBScript runtime error '800a01a8'

Object required: 'objConn'

/admin/newsletters/news_exec.asp, line 341

and, this is my code:
function openDB()
on error resume next
set objConn = server.createobject("adodb.connection")
objConn.Open "Driver={SQL Server};" &_
"Server=SCOTTM;" &_
"Database=testing;" &_
"Uid=matts;" &_
"Pwd=test;"
if err.number <> 0 then
dim msg
msg = "" _
& "<b>Number :</b> " & err.number & "<br><br>" _
& "<b>Page :</b> " & Request.ServerVariables("PATH_INFO") & "<br><br>" _
& "<b>Desc :</b> " & err.Description
response.Redirect "/includes/error.asp?msg=" & msg
end if
on error goto 0
end function



call openDb()
if I < ubound(custList,2) then
mySQL = "UPDATE tblNewsLetters " _
& "SET newsBookmark = '" & custList(0,I) & "' " _
& "WHERE idNews = " & idNews
else
mySQL = "UPDATE tblNewsLetters " _
& "SET newsBookmark = NULL " _
& "WHERE idNews = " & idNews
end if
set rs = objConn.Execute(mySQL)
call closeRS(rs)
call closedb()

Any input would also be helpful, thanks all.

 

[DotNetGnat]

i dont see

Set rs = Server.CreateObject("ADODB.Recordset")

but any way you dont need a recordset object for an update statement

so just say

objConn.execute(mySQL)

-DNG

 

[A1Pat]

Hi DotNetGnat,

I tried objConn.execute(mySQL) as you stated, and this is my ERROR:
ADODB.Connection error '800a0e78'

Operation is not allowed when the object is closed.

/admin/newsletters/news_exec.asp, line 341 ---> objConn.execute(mySQL)

Also, hope this help, the program also created many closeDB() function throught out the page.

 

[DotNetGnat]

hmm thats the problem...you are closing the object somewhere in the code earlier to the code you posted here...

any ways do this...

if objConn.State = 0 then
Set objConn = Server.CreateObject("ADODB.Connection")
End if
objConn.Execute(mySQL)

-DNG

 

[DotNetGnat]

oops...i meant this

if objConn.State = 0 then
'it means that the connection object is closed...so you 
'need to open it again..
call openDb()
End if
objConn.Execute(mySQL)

-DNG

 

[A1Pat]

In case you missed on my first message... the program does have that opendb() function stated on top of the mySQL statement, therefore, the database is supposed to be opened (as I understand), no matter what. But I tried your suggestion anyway and found the same ERROR.

 

[DotNetGnat]

are you sure you are not calling closedb() funciton some where before executing the objConn.Execute(mySQL)...

i am just guessing that you are calling your closedb() function, which closes the connection and because of which you are getting an error

"Operation is not allowed when the object is closed".

-DNG

 

[A1Pat]

Is there a way for me to display the whole page of ASP code on here, I don't want 400 lines of code all over the place in here.

please show me how to display CODE like you've done on your reply.

 

[DotNetGnat]

you need to put your code around the beginning and endinging CODE tags

check in the Process TGML section which is just below the reply window...

-DNG

 

[DotNetGnat]

something like this

 put your code here

-DNG

 

[A1Pat]

thanks...

<!--#include file="../../includes/checkadmin.asp"-->
<!--#include file="../../includes/functions.asp"-->
<!--#include file="../../includes/_email_.asp"-->
<%
'Declare variables
dim mySQL, cn, rs

'Newsletters
dim idNews
dim newsBookmark
dim newsSubj
dim newsBody
dim newsPreview
dim contType

'Work Fields
dim custType
dim custPaid
dim action
dim custList
dim I, I2

'Additional Newsletter Variables
dim custListEmail(20) 'Change to modify email batch size
dim strUA
dim nPctComplete

'*************************************************************************

'Get general form values
custType	= trim(Request.Form("custType"))
custPaid	= trim(Request.Form("custPaid"))
action		= trim(Request.Form("action"))

'Get newsletter form values
idNews		= trim(Request.Form("idNews"))
newsBookmark= trim(Request.Form("newsBookmark"))
newsSubj	= trim(Request.Form("newsSubj"))
newsBody	= trim(Request.Form("newsBody"))
newsPreview = trim(Request.Form("newsPreview"))
contType    = trim(Request.Form("contType"))
pEmailSales	= "phuynh@a1webcams.com"
pCompany	= "[URL unfurl="true"]www.A1webcams.com"[/URL]

'response.write "custType: " & custType & "<BR>"
'response.write "custPaid: " & custPaid & "<BR>"
'response.write "action: " & action & "<BR>"
'response.write "newsSubj: " & newsSubj & "<BR>"
'response.write "newsBody: " & newsBody & "<BR>"
'response.write "newsBookmark: " & newsBookmark & "<BR>"
'response.write "newsPreview: " & newsPreview & "<BR>"
'response.write "contType: " & contType & "<BR>"
'response.write "date: " & now() & "<BR>"
'response.write "date: " & dateInt(now()) & "<BR>"
'response.End()


'Check custType
if  custType <> "A" _
and custType <> "I" _
and custType <> "O" then
	response.redirect "/includes/error.asp?msg=" & server.URLEncode("Invalid Customer selection.")
end if

'Check custPaid
if custPaid <> "Y" then
	custPaid = "N"
end if

'Check Action
if  action <> "D" _
and action <> "F" _
and action <> "E" then
	response.redirect "/includes/error.asp?msg=" & server.URLEncode("Invalid Action selected.")
end if

'If newsletter, check that email is enabled
if action = "E" and mailComp = "0" then
	response.redirect "/includes/error.asp?msg=" & server.URLEncode("Email must be enabled in order to send newsletters.")
end if

'Check newsletter info
if action = "E" then
	if len(idNews) > 0 then
		idNews = cLng(idNews)
	end if
	if len(newsSubj) = 0 then
		response.redirect "/includes/error.asp?msg=" & server.URLEncode("Invalid Subject for Newsletter.")
	end if
	if len(newsBody) = 0 then
		response.redirect "/includes/error.asp?msg=" & server.URLEncode("Invalid Message for Newsletter.")
	end if
	if newsPreview <> "Y" then
		newsPreview = "N"
	end if
	if isNumeric(contType) then
		contType = CLng(contType)
	else
		contType = 0
	end if
end if

'If the user requested a Preview newsletter, we can skip most of the 
'code and just execute the bit of code below.
if action = "E" and newsPreview = "Y" then
%>
<!--#include file="../../includes/adminhtmltop.asp"-->
	<P align=left>
		<b><font size=3>Newsletters and Mailing Lists</font></b>
		<br><br>
	</P>
<%
	'Send emails
	on error resume next
	call sendmail (pCompany, pEmailSales, pEmailSales, newsSubj, newsBody, contType)
	if err.number <> 0 then
		response.redirect "/includes/error.asp?msg=" & server.URLEncode("An error occurred while sending email." & err.Description)
	end if
	on error goto 0
%>
	<span class="textBlockHead">Newsletter Preview</span><br>
	<table border=0 cellspacing=0 cellpadding=5 class="textBlock">
	<tr><td>
		A preview of the newsletter was sent to :<br><br>
		
		<b><font color=red><%=pEmailSales%></font></b><br><br>
		
		Check your Email Inbox to see if this email was delivered, 
		and if the content and format of the email is correct. Press 
		the '<b>BACK</b>' button on your browser to return to the 
		newsletter to make changes and submit another preview, or to 
		submit the newsletter to all your customers (remember to 
		un-check the 'Preview' box first).
	</td></tr>
	</table>
	<!--#include file="../../includes/adminhtmlbottom.asp"-->	
<%
	'Close Database
	call closedb()

	'End script execution here
	Response.End
end if

'Construct SQL statement
'Notes : 
'1. Because the recordset is assigned to an array using getRows(), the 
'   position of the columns in the SQL statement is important as the 
'   rest of the code expects the columns to be located at pre-determined 
'   indexes. Email = index 0, lastName = index 1, name = index 2.
'2. The sort order is also important due to the fact that the sort 
'   order field (in this case Email) is used as a bookmark to restart 
'   from a particular position onwards.
'
'Specify columns and table
mySQL = "SELECT fname, lname, email " _
	  & "FROM   tblCustomers " _
	  & "WHERE  1 = 1 "
'Opt-In customers
if custType = "I" then
	mySQL = mySQL & "AND futureMail = 'Y' "
end if
'Opt-Out customers
if custType = "O" then
	mySQL = mySQL & "AND (futureMail = 'N' OR futureMail = ' OR futureMail IS NULL) "
end if

'Start from a certain email
if action = "E" and len(newsBookmark) > 0 then
	mySQL = mySQL & "AND email > '" & newsBookmark & "' "
end if
'Sort the recordset
mySQL = mySQL & "ORDER BY email "

'Execute SQL query and assign recordset to an array
set rs = objConn.Execute(mySQL)
if rs.EOF then
	response.redirect "/includes/error.asp?msg=" & server.URLEncode("No customers were found matching your search criteria.")
else
	custList = rs.GetRows
end if
call closeRS(rs)

'If this is a newsletter, save the newsletter detail in the database
if action = "E" then

	'Check if we should UPDATE or INSERT newsletter info
	if len(idNews) > 0 and isNumeric(idNews) then
		mySQL = "UPDATE tblNewsLetters " _
			  & "SET    newsDate   = '" & now()						& "', " _
  			  & "       newsDateInt= '" & dateInt(now())			& "', " _
			  & "       newsSubj   = '" & replace(newsSubj,"'","'")& "', " _
			  & "       newsBody   = '" & replace(newsBody,"'","'")& "'  " _
			  & "WHERE  idNews = " & idNews
		set rs = objConn.Execute(mySQL)
		call closeRS(rs)
		
	else
		mySQL = "INSERT INTO tblNewsLetters ( newsDate," _
			  & " newsDateInt, newsSubj, newsBody " _
			  & ") VALUES ('" & Now  & "'," _
			  & "'" & dateInt(now()) & "'," _
			  & "'" & newsSubj		 & "'," _
			  & "'" & newsBody		 & "' " _
			  & ")"
		Set rs = objConn.Execute(mySQL)
		call closeRS(rs)
	
		mySQL = "SELECT MAX(idNews) AS maxID " _
		  & "	FROM   tblNewsLetters "
		Set rs = objConn.Execute(mySQL)
		idNews = rs("maxID") '@@identity
		call closeRS(rs)
	end if
	
end if

'Close Database
call closedb()

'DOWNLOAD customer list
if action = "F" then

	'Send headers for file name and content type changes
	Response.AddHeader "Content-Disposition", "attachment; filename=MailList.csv"
	Response.ContentType = "application/text"
	
	'Write results to output file
	for I = 0 to ubound(custList,2)
		Response.Write """" & custList(0,I) & """," & """" & custList(1,I) & "," & custList(2,I) & """" & vbCrLf
	next
	
	'End script execution
	Response.End
	
end if
%>
<!--#include file="../../includes/adminhtmlbottom.asp"-->	

<P align=left>
	<b><font size=3>Newsletters and Mailing Lists</font></b>
	<br><br>
</P>

<%
'NEWSLETTERS via email
if action = "E" then

	'Determine user agent (browser)
	strUA = Request.ServerVariables("HTTP_USER_AGENT")
	If InStr(UCase(strUA), "MSIE") Then
		strUA = "IE"
	else
		strUA = "NS"
	end if
%>
	<!-- Create Progress Bar Pop-Up Window -->
	<SCRIPT LANGUAGE="JavaScript">
	<!-- 
	var progWin     = null;
	var progWinOpen = 0;
	progWin = window.open("","progWin","width=180,height=120,toolbar=no,location=no,
directories=no,status=no,menubar=no,scrollbars=no,resizable=no,
copyhistory=no,screenX=20,screenY=40,left=20,top=40");

	progWin.document.write('<html><head><title>Progress Bar</title></head>');	
	progWin.document.write('<body style="font-family: Verdana, Arial; font-size: 8pt;" onLoad="window.opener.progWinOpen=1" onUnload="window.opener.progWinOpen=0">');
	progWin.document.write('<center>');
	progWin.document.write('  <b style="color: #800000; background-color: #dddddd; padding: 2px;">Progress</b><br><br>');
<%
	if strUA = "IE" then
%>
	progWin.document.write('  <DIV STYLE="width:100px;height:16px;border-width:1px;border-style:
solid;border-color:black" align="left">');
	progWin.document.write('    <DIV ID="progWinStatus" STYLE="width:0px;height:15px;background-color:red"></DIV>');
	progWin.document.write('  </DIV><br>');
<%
	else
%>
	progWin.document.write('  <form name=frmProgWin><b>Sent : </b><input type=text size=4 name=progWinStatus id=progWinStatus value=0></form>');
<%
	end if
%>
	progWin.document.write('  <b>Total To Send :</b> '+<%=ubound(custList,2)+1%>+'<br><br>');
	progWin.document.write('  <i>Please Wait ...</i>');
	progWin.document.write('</center>');
	progWin.document.write('</body>');
	progWin.document.write('</html>');
	
	progWin.document.close();
	//-->
	</SCRIPT>
<%
	'Send all buffered HTML to the browser. Without this, the user 
	'will wait until all processing has been completed before noticing 
	'a change in their browser, which may lead them to believe that 
	'nothing is happening. This will also pop-up the progress window.
	Response.Flush

	'Reset email array counter
	I2 = 0
%>
	<span class="textBlockHead">Newsletter Report</span><br>
	<table border=0 cellspacing=0 cellpadding=5 class="textBlock">
	<tr><td nowrap>
	
		<b>Total number of emails to send : <%=ubound(custList,2)+1%></b><br><br>
<%
		'Loop through recordset array. Emails are assigned to an email 
		'array (from the recordset array) so they can be sent in batches.
		for I = 0 to ubound(custList,2)
	
			'Move email address to email array
			custListEmail(I2) = custList(0,I)
			
			'Increment email array counter
			I2 = I2 + 1
			
			'Check if we need to send a batch of emails
			if I2 >= ubound(custListEmail) or I >= ubound(custList,2) then
			
				'Send a batch of emails
				on error resume next
				call sendmail (pCompany, pEmailSales, custListEmail, newsSubj, newsBody, contType)
				if err.number <> 0 then
					response.redirect "/includes/error.asp?msg=" & server.URLEncode("An error occurred while sending email." & err.Description)
				end if
				on error goto 0
				
				'Update checkpoint. We open and close the database 
				'connection before and after each update because the 
				'script may run for a very long time, so we need to 
				'ensure that other scripts also get an opportunity.
				call openDb()
				if I < ubound(custList,2) then
					mySQL = "UPDATE tblNewsLetters " _
						  & "SET    newsBookmark = '" & custList(0,I) & "' " _
						  & "WHERE  idNews = " & idNews
				else
					mySQL = "UPDATE tblNewsLetters " _
						  & "SET    newsBookmark = NULL " _
						  & "WHERE  idNews = " & idNews
				end if
				objConn.Execute(mySQL)
				call closeRS(rs)
				call closedb()
				
				'Display checkpoint
				Response.Write "Sent -&gt; <b>" & I+1 & "</b> emails <i>(Last email : " & custList(0,I) & ")</i><br>"
				
				'Reset email array counter and values
				I2 = 0
				erase custListEmail
				

				'Is the client still connected?
				if not Response.IsClientConnected then
					Response.End
				end if
				
				'Update Progress Window
				if strUA = "IE" then
					nPctComplete = ( (I+1) / (ubound(custList,2)+1)) * 100
					Response.Write "<SCRIPT LANGUAGE=""JavaScript"">if (progWinOpen==1) progWin.progWinStatus.style.width = " & nPctComplete & ";</SCRIPT>" & vbCrLf
				else
					Response.Write "<SCRIPT LANGUAGE=""JavaScript"">if (progWinOpen==1) progWin.document.frmProgWin.progWinStatus.value = " & I+1 & ";</SCRIPT>" & vbCrLf
				end if
				
				'Send buffered output to browser
				Response.Flush
				
			end if
			
		next
	
		'Close Progress Window
		Response.Write "<SCRIPT LANGUAGE=""JavaScript"">if (progWinOpen==1) progWin.close();</SCRIPT>" & vbCrLf
%>
		<br>
		<b>Total number of emails sent : <%=ubound(custList,2)+1%></b><br>
		
	</td></tr>
	</table>
<%
'DISPLAY customer list
else
%>
	<table border=1 cellspacing=1 cellpadding=3>
		<tr>
			<td bgcolor=#EEEEEE><b>Full Name</b></td>
			<td bgcolor=#EEEEEE><b>Email</b></td>
		</tr>
<%
		for I = 0 to ubound(custList,2)
			Response.Write "<tr><td>" & custList(1,I) & ", " & custList(0,I) & "</td><td>" & custList(2,I) & "</td></tr>"
		next
%>
	</table>
<%
end if
%>

 

[Chopstik]

If I understand you correctly, line 341 is highlighted in red below:

                'Update checkpoint. We open and close the database
                'connection before and after each update because the
                'script may run for a very long time, so we need to
                'ensure that other scripts also get an opportunity.
                call openDb()
                if I < ubound(custList,2) then
                    mySQL = "UPDATE tblNewsLetters " _
                          & "SET    newsBookmark = '" & custList(0,I) & "' " _
                          & "WHERE  idNews = " & idNews
                else
                    mySQL = "UPDATE tblNewsLetters " _
                          & "SET    newsBookmark = NULL " _
                          & "WHERE  idNews = " & idNews
                end if
                [COLOR=red]objConn.Execute(mySQL)[/color]
                call closeRS(rs)
                call closedb()

I believe your problem has to do with the fact that you are calling your openDB() function, which holds the objConn object. Is your objConn object available outside of the that function? I don't believe it is (but I may be wrong). You may need to pass the SQL string back to the function itself and run the .Execute inside of the function instead of inside. At least, that is how I would do it. Try it and see if that will help. See below for how I would modify your function (you may need to modify as I'm doing this off the top of my head without testing):

function openDB(strSQL)
    on error resume next
    set objConn = server.createobject("adodb.connection")
    objConn.Open "Driver={SQL Server};" &_
        "Server=SCOTTM;" &_
        "Database=testing;" &_
        "Uid=matts;" &_
        "Pwd=test;"
[COLOR=red]    objConn.Execute(mySQL)
    objConn.Close [/color]
    if err.number <> 0 then
        dim msg
        msg = "" _
            & "<b>Number :</b> " & err.number & "<br><br>" _
            & "<b>Page :</b> "   & Request.ServerVariables("PATH_INFO") & "<br><br>" _
            & "<b>Desc :</b> "   & err.Description
        response.Redirect "/includes/error.asp?msg=" & msg
    end if
    on error goto 0
end function 
[COLOR=green]... 'Go to the place where you were calling it before and modify as follows. [/color]
                if I < ubound(custList,2) then
                    mySQL = "UPDATE tblNewsLetters " _
                          & "SET    newsBookmark = '" & custList(0,I) & "' " _
                          & "WHERE  idNews = " & idNews
                else
                    mySQL = "UPDATE tblNewsLetters " _
                          & "SET    newsBookmark = NULL " _
                          & "WHERE  idNews = " & idNews
                end if
                call openDb(mySQL)

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[DotNetGnat]

see...as i expected, you are closing the connection before you execute your query...

<%
'Close Database
[red] call closedb()[/red]

-DNG

 

[A1Pat]

Aha...
You did it, Chopstik. Great job!!!

If you don't mind, could you please explain for me the situation.

thanks again.

 

[A1Pat]

I see, DotNetGnat. Could you show me where that closedb() function that is related to the error (there's more than 1 as I can see). That will greatly help me to understand this problem better.

thanks.

 

[Chopstik]

You created your objConn variable inside of a function. Then you were attempting to reference it outside of that function where it does not exist. Ergo, you were receiving an error message that the object was closed (or, in this case, did not exist). So, either you reference the object ONLY in your function or you make it available to the entire page (by removing it from the function). Does this make sense?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[DotNetGnat]

more lines for reference

.....
  <!--#include file="../../includes/adminhtmlbottom.asp"-->    
<%
    'Close Database
    [red]call closedb()[/red]

    'End script execution here
    Response.End
end if
....

-DNG

 

[Chopstik]

Actually, with deference to DNG, your closeDB() function was not causing this issue because the variable was not available outside of the openDB() function. If anything, it was probably causing more of an issue if it would have hit there because the connection object still existed only in the openDB() function.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[A1Pat]

To DotNetGnat,

Your point is well-taken, however, before I came to this forum, I had already tried to eliminate all "call closedb()" function code out of the page (including the one your mentioned); the ERROR was still there. So I can tell that this is not the case, but I do understand why your suspection.

Thank you for your input.

Again, thank you Chopstik for the explaination. I understood now.

Keep up the good works, guys.

 

[DotNetGnat]

I concur with Chopstik.

thanks

-DNG