Access Function Objects Outside Function

[Soniad]

Hi,

I have created a function which connects to excel DB and fecth records from excel file.
Now, I access this function in a SUB routine.Everyth ing is fine ,but after doing the process I want to free the objects (used in function).When I access this object and set to nothing then I get error message that "object required".
Even I have declared the objects using DIM on top of the page.but not working.

Regards,

"D"

[jhardman]

I think I'd have to see the code to tell where this had gone wrong. I would guess that the object is actually called once after you destroyed it.

Jared

[Soniad]

Hi,

Here is rough part of code :

<%

'PUBLIC OBJECT DECLARATIONS
Dim cnnExcel,rstExcel,LoopExcel,StrType,UserFilePath

UserFilePath = Server.Mappath("filename.xls")

' FUNCTION TO CONNECT TO AND RETREIVE DATA FROM EXCEL FILE ON SERVER
' ****************************************************************************	
FUNCTION ExtractEls()

		   'Response.Write("Test Function")
	 
			' This is all standard ADO except for the connection string.
			' You can also use a DSN instead
			Set cnnExcel = Server.CreateObject("ADODB.Connection")
			cnnExcel.Open "DBQ=" & UserFilePath&"\LICNav.xls" & ";" & _
			"DRIVER={Microsoft Excel Driver (*.xls)};"
			
			' Same as any other data source.
			' LIC is my named range in the Excel file
			Set rstExcel = Server.CreateObject("ADODB.Recordset")
			rstExcel.Open "SELECT * FROM [LIC$];", cnnExcel
			
			Set ExtractEls = rstExcel


			 
END FUNCTION
' END EXCEL
' ****************************************************************************	


' PROCEDURE  TO CHECK SQL DATA
' ****************************************************************************	
SUB SAVEDATA()

			
		    SET LoopExcel = ExtractEls()
	

			' Loop through the data rows and get the values from excel file and insert into SQL Table.
			'objCnn.BeginTrans
	
			If Not LoopExcel.EOF Then

				' Check If details for  Date given in excel is present or not
				'  ******************		
				
				Dim StrCheckData,RstCheckData
				
				StrCheckData = "Query"
		
				'Response.Write(StrCheckData&"<BR>")
				Set RstCheckData = GetRecordset(StrCheckData, null, null, null, null, null, null, null, null)		
				
		
				'  If details  for Date given in excel is present then show message 
				'  ******************		
				IF NOT RstCheckData.EOF THEN   
				
					StrType = "CONFIRM:"&LoopExcel.Fields("NAV Date")
					'CALL FUNCTION TO REDIRECT TO PAGE
					CALL RedirectFunc()

				'  If details  for Date given in excel is not present then fetch records from excel file and insert in table
				'  ******************		
				ELSE
				
				    ' CALL FUNCTION TO INSERT DATA
				 	CALL DATAINSERT()
			
			End If
	
				'ERROR HANDLING 
				If  Err.number <> 0  or objCnn.Errors.Count <> 0 Then	'Error Occured
					strMsgText = GetErrorMsg(null,0,Err)
					'objCnn.RollbackTrans
					%>
					<!--SHOW ERROR MESSAGE PAGE-->
				<%End If	
		
				If  Err.number = 0  and  objCnn.Errors.Count = 0 Then	'No Error Occured
					'objCnn.CommitTrans
					'Response.Write("DATA INSERTED...")
				End If			
				' END ERROR HANDLING
				
		
	    '  End 
	    '  ******************				
	  END IF

	  IF RstCheckData.State = 1 THEN RstCheckData.Close
	  SET RstCheckData = NOTHING	

	  SET LoopExcel = NOTHING
	
END SUB
' END SQL DATA CHECK
' ****************************************************************************	


' PROCEDURE TO INSERT DATA
' ****************************************************************************	
SUB DATAINSERT()
				 				
				 Dim StrSavData,strMsgText,StrDateFormat,i

				 SET LoopExcel = ExtractEls()
				
				' Loop through the data rows and get the values from excel file and insert into SQL Table.
				'objCnn.BeginTrans				

				 If Not LoopExcel.EOF Then
				 i = 0 

						StrType = "SHOWDATA:"&LoopExcel.Fields("NAV Date")	
				 				 
						' CREATE TABLE TO SHOW AFTER INSERTING DATA
						' CREATE TABLE HEADINGS
						StrTable = "<Table border=0 align=center width=800> " _				
								 & "<Tr class=CtntTblHd height=30><Td ColSpan=4 align=center>Following details are stored.</Td></Tr> " _				
								 & "<Tr class=CtntTblHd> " _
								 & "<Td> " & LoopExcel.Fields.Item(1).Name & "</Td> " & vbCrLf  _
								 & "<Td> " & LoopExcel.Fields.Item(2).Name & "</Td> " & vbCrLf  _
								 & "<Td> " & LoopExcel.Fields.Item(3).Name & "</Td> " & vbCrLf  _
								 & "<Td> " & LoopExcel.Fields.Item(0).Name & "</Td> " & vbCrLf  _
								 & "</Tr> "						
							 
					 While Not LoopExcel.EOF

						StrDateFormat = day(LoopExcel.Fields("NAV Date"))&"/"&month(LoopExcel.Fields("NAV Date"))&"/"&year(LoopExcel.Fields("NAV Date"))							  
				
						 ' CREATE ROWS
						StrTable = StrTable & "<Tr> " _
								 & "<Td class=CtntTblbody> " & LoopExcel.Fields("Fund ID") & "</Td> " & vbCrLf  _
								 & "<Td class=CtntTblbody align=left> " & LoopExcel.Fields("Fund Name") & "</Td> " & vbCrLf _ 
								 & "<Td class=CtntTblbody> " & LoopExcel.Fields("NAV") & "</Td> " & vbCrLf  _
								 & "<Td class=CtntTblbody> " & displaydate(StrDateFormat) & "</Td> " & vbCrLf  _
								 & "</Tr> "	
								 
					'i = i + 1										 						
					 rstExcel.MoveNext
		
					 Wend
			 
						' TABLE ENDS			 
						StrTable = StrTable & "</Table> "	
				End If	
			
			    'Response.Write(i&" - NO OF DATA ROWS<BR>")     'NO OF DATA ROWS EXCEPT THE ROWS CONTAINING COLUMNS NAMES
			

				'ERROR HANDLING 
				If  Err.number <> 0  or objCnn.Errors.Count <> 0 Then	'Error Occured
					strMsgText = GetErrorMsg(null,0,Err)
					'objCnn.RollbackTrans
					%>
										<!--SHOW ERROR MESSAGE PAGE-->
				<%End If	
		
				If  Err.number = 0  and  objCnn.Errors.Count = 0 Then	'No Error Occured
					'objCnn.CommitTrans
					'Response.Write("DATA INSERTED...")
				End If			
				' END ERROR HANDLING			
				
				SET LoopExcel = NOTHING
				
				'CALL FUNCTION TO REDIRECT TO PAGE		
				CALL RedirectFunc()
				
END SUB
' END INSERT
' ****************************************************************************	


' REDIRECTS TO PAGE AFTER COMPLETING PROCEDURE
' ****************************************************************************		
SUB RedirectFunc()
		
'	Response.Write(StrType&"<BR>")
'	Response.Write(StrTable&"<BR>")
'	Response.End() 

		
	'DO NOT COMMENT FOLLOWING SATETEMENT,IT IS NECESSARY WHILE POSTING THE PAGE	
	Response.Write("<form name = 'FORM1' method='post'><input type='hidden' name = 'hidTable' value = '"&StrTable&"'></form>")	
%>
 

<script language="javascript">
	FORM1.action = "TEST.asp?StrType=<%=StrType%>";
	FORM1.submit();
</script>
 

<%END SUB
' END REDIRECT
' ****************************************************************************		
%>

Regards,

"D"

[jhardman]

Hi,

Here is rough part of code :

<%

'PUBLIC OBJECT DECLARATIONS
Dim cnnExcel,rstExcel,LoopExcel,StrType,UserFilePath

UserFilePath = Server.Mappath("filename.xls")

' FUNCTION TO CONNECT TO AND RETREIVE DATA FROM EXCEL FILE ON SERVER
' ****************************************************************************	
FUNCTION ExtractEls()

		   'Response.Write("Test Function")
	 
			' This is all standard ADO except for the connection string.
			' You can also use a DSN instead
			Set cnnExcel = Server.CreateObject("ADODB.Connection")
			cnnExcel.Open "DBQ=" & UserFilePath&"\LICNav.xls" & ";" & _
			"DRIVER={Microsoft Excel Driver (*.xls)};"
			
			' Same as any other data source.
			' LIC is my named range in the Excel file
			Set rstExcel = Server.CreateObject("ADODB.Recordset")
			rstExcel.Open "SELECT * FROM [LIC$];", cnnExcel
			
			Set ExtractEls = rstExcel


			 
END FUNCTION
' END EXCEL
' ****************************************************************************	


' PROCEDURE  TO CHECK SQL DATA
' ****************************************************************************	
SUB SAVEDATA()

			
		    SET LoopExcel = ExtractEls()
	

			' Loop through the data rows and get the values from excel file and insert into SQL Table.
			'objCnn.BeginTrans
	
			If Not LoopExcel.EOF Then

				' Check If details for  Date given in excel is present or not
				'  ******************		
				
				Dim StrCheckData,RstCheckData
				
				StrCheckData = "Query"
		
				'Response.Write(StrCheckData&"<BR>")
				Set RstCheckData = GetRecordset(StrCheckData, null, null, null, null, null, null, null, null)		
				
		
				'  If details  for Date given in excel is present then show message 
				'  ******************		
				IF NOT RstCheckData.EOF THEN   
				
					StrType = "CONFIRM:"&LoopExcel.Fields("NAV Date")
					'CALL FUNCTION TO REDIRECT TO PAGE
					CALL RedirectFunc()

				'  If details  for Date given in excel is not present then fetch records from excel file and insert in table
				'  ******************		
				ELSE
				
				    ' CALL FUNCTION TO INSERT DATA
				 	CALL DATAINSERT()
			
			End If
	
				'ERROR HANDLING 
				If  Err.number <> 0  or objCnn.Errors.Count <> 0 Then	'Error Occured
					strMsgText = GetErrorMsg(null,0,Err)
					'objCnn.RollbackTrans
					%>
					<!--SHOW ERROR MESSAGE PAGE-->
				<%End If	
		
				If  Err.number = 0  and  objCnn.Errors.Count = 0 Then	'No Error Occured
					'objCnn.CommitTrans
					'Response.Write("DATA INSERTED...")
				End If			
				' END ERROR HANDLING
				
		
	    '  End 
	    '  ******************				
	  END IF

	  IF RstCheckData.State = 1 THEN RstCheckData.Close
	  SET RstCheckData = NOTHING	

	  SET LoopExcel = NOTHING
	
END SUB
' END SQL DATA CHECK
' ****************************************************************************	


' PROCEDURE TO INSERT DATA
' ****************************************************************************	
SUB DATAINSERT()
				 				
				 Dim StrSavData,strMsgText,StrDateFormat,i

				 SET LoopExcel = ExtractEls()
				
				' Loop through the data rows and get the values from excel file and insert into SQL Table.
				'objCnn.BeginTrans				

				 If Not LoopExcel.EOF Then
				 i = 0 

						StrType = "SHOWDATA:"&LoopExcel.Fields("NAV Date")	
				 				 
						' CREATE TABLE TO SHOW AFTER INSERTING DATA
						' CREATE TABLE HEADINGS
						StrTable = "<Table border=0 align=center width=800> " _				
								 & "<Tr class=CtntTblHd height=30><Td ColSpan=4 align=center>Following details are stored.</Td></Tr> " _				
								 & "<Tr class=CtntTblHd> " _
								 & "<Td> " & LoopExcel.Fields.Item(1).Name & "</Td> " & vbCrLf  _
								 & "<Td> " & LoopExcel.Fields.Item(2).Name & "</Td> " & vbCrLf  _
								 & "<Td> " & LoopExcel.Fields.Item(3).Name & "</Td> " & vbCrLf  _
								 & "<Td> " & LoopExcel.Fields.Item(0).Name & "</Td> " & vbCrLf  _
								 & "</Tr> "						
							 
					 While Not LoopExcel.EOF

						StrDateFormat = day(LoopExcel.Fields("NAV Date"))&"/"&month(LoopExcel.Fields("NAV Date"))&"/"&year(LoopExcel.Fields("NAV Date"))							  
				
						 ' CREATE ROWS
						StrTable = StrTable & "<Tr> " _
								 & "<Td class=CtntTblbody> " & LoopExcel.Fields("Fund ID") & "</Td> " & vbCrLf  _
								 & "<Td class=CtntTblbody align=left> " & LoopExcel.Fields("Fund Name") & "</Td> " & vbCrLf _ 
								 & "<Td class=CtntTblbody> " & LoopExcel.Fields("NAV") & "</Td> " & vbCrLf  _
								 & "<Td class=CtntTblbody> " & displaydate(StrDateFormat) & "</Td> " & vbCrLf  _
								 & "</Tr> "	
								 
					'i = i + 1										 						
					 rstExcel.MoveNext
		
					 Wend
			 
						' TABLE ENDS			 
						StrTable = StrTable & "</Table> "	
				End If	
			
			    'Response.Write(i&" - NO OF DATA ROWS<BR>")     'NO OF DATA ROWS EXCEPT THE ROWS CONTAINING COLUMNS NAMES
			

				'ERROR HANDLING 
				If  Err.number <> 0  or objCnn.Errors.Count <> 0 Then	'Error Occured
					strMsgText = GetErrorMsg(null,0,Err)
					'objCnn.RollbackTrans
					%>
										<!--SHOW ERROR MESSAGE PAGE-->
				<%End If	
		
				If  Err.number = 0  and  objCnn.Errors.Count = 0 Then	'No Error Occured
					'objCnn.CommitTrans
					'Response.Write("DATA INSERTED...")
				End If			
				' END ERROR HANDLING			
				
				SET LoopExcel = NOTHING
				
				'CALL FUNCTION TO REDIRECT TO PAGE		
				CALL RedirectFunc()
				
END SUB
' END INSERT
' ****************************************************************************	


' REDIRECTS TO PAGE AFTER COMPLETING PROCEDURE
' ****************************************************************************		
SUB RedirectFunc()
		
'	Response.Write(StrType&"<BR>")
'	Response.Write(StrTable&"<BR>")
'	Response.End() 

		
	'DO NOT COMMENT FOLLOWING SATETEMENT,IT IS NECESSARY WHILE POSTING THE PAGE	
	Response.Write("<form name = 'FORM1' method='post'><input type='hidden' name = 'hidTable' value = '"&StrTable&"'></form>")	
%>
 

<script language="javascript">
	FORM1.action = "TEST.asp?StrType=<%=StrType%>";
	FORM1.submit();
</script>
 

<%END SUB
' END REDIRECT
' ****************************************************************************		
%>

Regards,

"D"

and what line gives you the error?

Jared

[Soniad]

and what line gives you the error?

Jared

<%
' REDIRECTS TO PAGE AFTER COMPLETING PROCEDURE 
' ****************************************************************************         
SUB RedirectFunc() 
  
'    Response.Write(StrType&"<BR>") 
'    Response.Write(StrTable&"<BR>") 
'    Response.End()  

'	----------Error AT THIS LINE----------
	Set cnnExcel = Nothing
'	------------------------------
	
  
    'DO NOT COMMENT FOLLOWING SATETEMENT,IT IS NECESSARY WHILE POSTING THE PAGE     
    Response.Write("<form name = 'FORM1' method='post'><input type='hidden' name = 'hidTable' value = '"&StrTable&"'></form>")     
%>

Regards,

"D"

[jhardman]

<%
' REDIRECTS TO PAGE AFTER COMPLETING PROCEDURE 
' ****************************************************************************         
SUB RedirectFunc() 
  
'    Response.Write(StrType&"<BR>") 
'    Response.Write(StrTable&"<BR>") 
'    Response.End()  

'	----------Error AT THIS LINE----------
	Set cnnExcel = Nothing
'	------------------------------
	
  
    'DO NOT COMMENT FOLLOWING SATETEMENT,IT IS NECESSARY WHILE POSTING THE PAGE     
    Response.Write("<form name = 'FORM1' method='post'><input type='hidden' name = 'hidTable' value = '"&StrTable&"'></form>")     
%>

Regards,

"D"

try putting that line at the end of the ExtractEls() function (I think everything should still work)

Jared