Database connections using ASP

[Atirez]

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.

<%

	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

[dhtmlgod]

Could you post the exact error and line number?

[Atirez]

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

[dhtmlgod]

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?

[sylow]

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