468,544 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,544 developers. It's quick & easy.

Access Function Objects Outside Function

Soniad
66
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.Everything 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"
Apr 9 '10 #1
5 1579
jhardman
3,405 Expert 2GB
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
Apr 10 '10 #2
Soniad
66
Hi,

Here is rough part of code :

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. 'PUBLIC OBJECT DECLARATIONS
  4. Dim cnnExcel,rstExcel,LoopExcel,StrType,UserFilePath
  5.  
  6. UserFilePath = Server.Mappath("filename.xls")
  7.  
  8. ' FUNCTION TO CONNECT TO AND RETREIVE DATA FROM EXCEL FILE ON SERVER
  9. ' ****************************************************************************    
  10. FUNCTION ExtractEls()
  11.  
  12.            'Response.Write("Test Function")
  13.  
  14.             ' This is all standard ADO except for the connection string.
  15.             ' You can also use a DSN instead
  16.             Set cnnExcel = Server.CreateObject("ADODB.Connection")
  17.             cnnExcel.Open "DBQ=" & UserFilePath&"\LICNav.xls" & ";" & _
  18.             "DRIVER={Microsoft Excel Driver (*.xls)};"
  19.  
  20.             ' Same as any other data source.
  21.             ' LIC is my named range in the Excel file
  22.             Set rstExcel = Server.CreateObject("ADODB.Recordset")
  23.             rstExcel.Open "SELECT * FROM [LIC$];", cnnExcel
  24.  
  25.             Set ExtractEls = rstExcel
  26.  
  27.  
  28.  
  29. END FUNCTION
  30. ' END EXCEL
  31. ' ****************************************************************************    
  32.  
  33.  
  34. ' PROCEDURE  TO CHECK SQL DATA
  35. ' ****************************************************************************    
  36. SUB SAVEDATA()
  37.  
  38.  
  39.             SET LoopExcel = ExtractEls()
  40.  
  41.  
  42.             ' Loop through the data rows and get the values from excel file and insert into SQL Table.
  43.             'objCnn.BeginTrans
  44.  
  45.             If Not LoopExcel.EOF Then
  46.  
  47.                 ' Check If details for  Date given in excel is present or not
  48.                 '  ******************        
  49.  
  50.                 Dim StrCheckData,RstCheckData
  51.  
  52.                 StrCheckData = "Query"
  53.  
  54.                 'Response.Write(StrCheckData&"<BR>")
  55.                 Set RstCheckData = GetRecordset(StrCheckData, null, null, null, null, null, null, null, null)        
  56.  
  57.  
  58.                 '  If details  for Date given in excel is present then show message 
  59.                 '  ******************        
  60.                 IF NOT RstCheckData.EOF THEN   
  61.  
  62.                     StrType = "CONFIRM:"&LoopExcel.Fields("NAV Date")
  63.                     'CALL FUNCTION TO REDIRECT TO PAGE
  64.                     CALL RedirectFunc()
  65.  
  66.                 '  If details  for Date given in excel is not present then fetch records from excel file and insert in table
  67.                 '  ******************        
  68.                 ELSE
  69.  
  70.                     ' CALL FUNCTION TO INSERT DATA
  71.                      CALL DATAINSERT()
  72.  
  73.             End If
  74.  
  75.                 'ERROR HANDLING 
  76.                 If  Err.number <> 0  or objCnn.Errors.Count <> 0 Then    'Error Occured
  77.                     strMsgText = GetErrorMsg(null,0,Err)
  78.                     'objCnn.RollbackTrans
  79.                     %>
  80.                     <!--SHOW ERROR MESSAGE PAGE-->
  81.                 <%End If    
  82.  
  83.                 If  Err.number = 0  and  objCnn.Errors.Count = 0 Then    'No Error Occured
  84.                     'objCnn.CommitTrans
  85.                     'Response.Write("DATA INSERTED...")
  86.                 End If            
  87.                 ' END ERROR HANDLING
  88.  
  89.  
  90.         '  End 
  91.         '  ******************                
  92.       END IF
  93.  
  94.       IF RstCheckData.State = 1 THEN RstCheckData.Close
  95.       SET RstCheckData = NOTHING    
  96.  
  97.       SET LoopExcel = NOTHING
  98.  
  99. END SUB
  100. ' END SQL DATA CHECK
  101. ' ****************************************************************************    
  102.  
  103.  
  104. ' PROCEDURE TO INSERT DATA
  105. ' ****************************************************************************    
  106. SUB DATAINSERT()
  107.  
  108.                  Dim StrSavData,strMsgText,StrDateFormat,i
  109.  
  110.                  SET LoopExcel = ExtractEls()
  111.  
  112.                 ' Loop through the data rows and get the values from excel file and insert into SQL Table.
  113.                 'objCnn.BeginTrans                
  114.  
  115.                  If Not LoopExcel.EOF Then
  116.                  i = 0 
  117.  
  118.                         StrType = "SHOWDATA:"&LoopExcel.Fields("NAV Date")    
  119.  
  120.                         ' CREATE TABLE TO SHOW AFTER INSERTING DATA
  121.                         ' CREATE TABLE HEADINGS
  122.                         StrTable = "<Table border=0 align=center width=800> " _                
  123.                                  & "<Tr class=CtntTblHd height=30><Td ColSpan=4 align=center>Following details are stored.</Td></Tr> " _                
  124.                                  & "<Tr class=CtntTblHd> " _
  125.                                  & "<Td> " & LoopExcel.Fields.Item(1).Name & "</Td> " & vbCrLf  _
  126.                                  & "<Td> " & LoopExcel.Fields.Item(2).Name & "</Td> " & vbCrLf  _
  127.                                  & "<Td> " & LoopExcel.Fields.Item(3).Name & "</Td> " & vbCrLf  _
  128.                                  & "<Td> " & LoopExcel.Fields.Item(0).Name & "</Td> " & vbCrLf  _
  129.                                  & "</Tr> "                        
  130.  
  131.                      While Not LoopExcel.EOF
  132.  
  133.                         StrDateFormat = day(LoopExcel.Fields("NAV Date"))&"/"&month(LoopExcel.Fields("NAV Date"))&"/"&year(LoopExcel.Fields("NAV Date"))                              
  134.  
  135.                          ' CREATE ROWS
  136.                         StrTable = StrTable & "<Tr> " _
  137.                                  & "<Td class=CtntTblbody> " & LoopExcel.Fields("Fund ID") & "</Td> " & vbCrLf  _
  138.                                  & "<Td class=CtntTblbody align=left> " & LoopExcel.Fields("Fund Name") & "</Td> " & vbCrLf _ 
  139.                                  & "<Td class=CtntTblbody> " & LoopExcel.Fields("NAV") & "</Td> " & vbCrLf  _
  140.                                  & "<Td class=CtntTblbody> " & displaydate(StrDateFormat) & "</Td> " & vbCrLf  _
  141.                                  & "</Tr> "    
  142.  
  143.                     'i = i + 1                                                                 
  144.                      rstExcel.MoveNext
  145.  
  146.                      Wend
  147.  
  148.                         ' TABLE ENDS             
  149.                         StrTable = StrTable & "</Table> "    
  150.                 End If    
  151.  
  152.                 'Response.Write(i&" - NO OF DATA ROWS<BR>")     'NO OF DATA ROWS EXCEPT THE ROWS CONTAINING COLUMNS NAMES
  153.  
  154.  
  155.                 'ERROR HANDLING 
  156.                 If  Err.number <> 0  or objCnn.Errors.Count <> 0 Then    'Error Occured
  157.                     strMsgText = GetErrorMsg(null,0,Err)
  158.                     'objCnn.RollbackTrans
  159.                     %>
  160.                                         <!--SHOW ERROR MESSAGE PAGE-->
  161.                 <%End If    
  162.  
  163.                 If  Err.number = 0  and  objCnn.Errors.Count = 0 Then    'No Error Occured
  164.                     'objCnn.CommitTrans
  165.                     'Response.Write("DATA INSERTED...")
  166.                 End If            
  167.                 ' END ERROR HANDLING            
  168.  
  169.                 SET LoopExcel = NOTHING
  170.  
  171.                 'CALL FUNCTION TO REDIRECT TO PAGE        
  172.                 CALL RedirectFunc()
  173.  
  174. END SUB
  175. ' END INSERT
  176. ' ****************************************************************************    
  177.  
  178.  
  179. ' REDIRECTS TO PAGE AFTER COMPLETING PROCEDURE
  180. ' ****************************************************************************        
  181. SUB RedirectFunc()
  182.  
  183. '    Response.Write(StrType&"<BR>")
  184. '    Response.Write(StrTable&"<BR>")
  185. '    Response.End() 
  186.  
  187.  
  188.     'DO NOT COMMENT FOLLOWING SATETEMENT,IT IS NECESSARY WHILE POSTING THE PAGE    
  189.     Response.Write("<form name = 'FORM1' method='post'><input type='hidden' name = 'hidTable' value = '"&StrTable&"'></form>")    
  190. %>
  191.  
  192.  
  193. <script language="javascript">
  194.     FORM1.action = "TEST.asp?StrType=<%=StrType%>";
  195.     FORM1.submit();
  196. </script>
  197.  
  198.  
  199. <%END SUB
  200. ' END REDIRECT
  201. ' ****************************************************************************        
  202. %>
  203.  
  204.  
  205.  

Regards,

"D"
Apr 12 '10 #3
jhardman
3,405 Expert 2GB
@Soniad
and what line gives you the error?

Jared
Apr 20 '10 #4
Soniad
66
@jhardman
Expand|Select|Wrap|Line Numbers
  1. <%
  2. ' REDIRECTS TO PAGE AFTER COMPLETING PROCEDURE 
  3. ' ****************************************************************************         
  4. SUB RedirectFunc() 
  5.  
  6. '    Response.Write(StrType&"<BR>") 
  7. '    Response.Write(StrTable&"<BR>") 
  8. '    Response.End()  
  9.  
  10. '    ----------Error AT THIS LINE----------
  11.     Set cnnExcel = Nothing
  12. '    ------------------------------
  13.  
  14.  
  15.     'DO NOT COMMENT FOLLOWING SATETEMENT,IT IS NECESSARY WHILE POSTING THE PAGE     
  16.     Response.Write("<form name = 'FORM1' method='post'><input type='hidden' name = 'hidTable' value = '"&StrTable&"'></form>")     
  17. %> 
  18.  
Regards,

"D"
Apr 20 '10 #5
jhardman
3,405 Expert 2GB
@Soniad
try putting that line at the end of the ExtractEls() function (I think everything should still work)

Jared
Apr 20 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by TheFerryman | last post: by
17 posts views Thread by Jelmer | last post: by
49 posts views Thread by Yannick Turgeon | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.