SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot Atirez's Avatar
    Join Date
    Nov 2000
    Location
    England
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database connections using ASP

    I am trying to update multiple records (on an Access 2000 DB) in ASP, but I keep getting an error when trying to pass more than one record at a time or when trying to update a different record after I have used my script succsefully.

    The error I am recieving is unspecified but it reverts to the connection in the script. Would someone please have a look at the script and tell me if it something I have done or if it is a problem with the database.

    Code:
    <%
    
    	Dim queryString,MyArray
    	queryString = Request.QueryString("MyArray")
    	
    	MyArray=Split(QueryString,"x")
    	
    Dim NbRec,n
    Dim cnn,leav,strProvider
    
    n=0
    NbRec=(len(QueryString)/2)
    
    Do While Not n=NbRec
    
    strProvider = "Driver={Microsoft Access Driver (*.mdb)};DBQ=d:\inetpub\wwwroot\fpdb\ladb.mdb;"
    Set cnn = Server.CreateObject("ADODB.Connection")
    cnn.Open strProvider
    set cm = Server.CreateObject("ADODB.Command")
    cm.ActiveConnection = cnn
    
    
    leav = MyArray(n)
    response.write leav & Chr(10)
    cm.CommandText = "UPDATE leave SET csprocessed=Yes WHERE leaveid=" & leav & ";"
    cm.execute
    
    If err.number>0 then
       		response.write "VBScript Errors Occured:" & "<P>"
        	response.write "Error Number=" & err.number & "<P>"
        	response.write "Error Descr.=" & err.description & "<P>"
        	response.write "Help Context=" & err.helpcontext & "<P>" 
        	response.write "Help Path=" & err.helppath & "<P>"
        	response.write "Native Error=" & err.nativeerror & "<P>"
        	response.write "Source=" & err.source & "<P>"
        	response.write "SQLState=" & err.sqlstate & "<P>"
       	end if
       	IF cnn.errors.count> 0 then
        	response.write "Database Errors Occured" & "<P>"
        	response.write SQLstmt & "<P>"
       		for counter= 0 to cnn.errors.count
        		response.write "Error #" & cnn.errors(counter).number & "<P>"
        		response.write "Error desc. -> " & cnn.errors(counter).description & "<P>"
       		next
       	else
        	response.write "<br><br><font face='arial' size=4><b>"
    		response.write "Thank you! Your request has been forwarded to Student Services.</font><br>"
        	response.write "<input type='button' value='Close Window' onClick='window.close()' id=button1 name=button1 style='font-size: 10pt; font-family: Arial; font-weight: bold'><p>"
    		end if
    
    n=n+1
    
    cnn.Close
    ' Set cnn = nothing
    
    Loop
    %>
    Thanx in advance

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you post the exact error and line number?


  3. #3
    SitePoint Zealot Atirez's Avatar
    Join Date
    Nov 2000
    Location
    England
    Posts
    173
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, here it is:

    Error number: 3709
    Error Descr: The application requested an operation on an object with a reference to a closed or invalid Connection object.
    Source=Microsoft VBScript runtime error
    Database Errors Occured
    Error #-2147467259
    Error desc. -> Unspecified error

    The error occurs on this line:
    cnn.Open strProvider

  4. #4
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You sure it ocures on that line? Thats the first time I'ev seen this error on that line... what are you running this ASP script on?

  5. #5
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cnn.Close
    ' Set cnn = nothing

    loop
    change it to


    loop
    cnn.Close
    Set cnn = nothing
    also move ur connection before loop starts
    u will leave pool breathless if u create one connection all the time.
    Create connection only 1 time and use through all the page and close when u do not need any database functionality


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •