Array looping problem in asp page using vbscript

[ejbauer]

I have some code which is listed below. The code will work the first time through. The problem is that when it loops, it does not seem to be reopening the data connection. Can anyone figure this one out?

<% Dim GamArray
GamArray= Split(Gam,",")
Dim Counter
For Counter=LBound(GamArray) to UBound(GamArray)
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "DATABASE=runtime;DSN=WEBSQL;UID=wwuser;Password=wwuser"
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConn cmdDC.CommandText = "select Value,convert(Varchar,datetime,101)Date,DatePart(HH,datetime)Hour,DatePart(n, datetime)Minute from History where tagname='FGam"&GamArray(Counter)&"_OEE' and datetime between'"& QueryStart &"' and '"& QueryStop &"' and value is not NULL AND wwVersion = 'Latest' AND wwRetrievalMode = 'Cyclic' and wwresolution=300000"
cmdDC.CommandType = 1
Set rsDC1 = Server.CreateObject("ADODB.Recordset")
rsDC1.Open cmdDC, , 0, 1

If Not rsDC1.EOF Then rsDC1.MoveFirst
Do While Not rsDC1.EOF
Graph1= Graph1 & rsDC1("Date") &" "& rsDC1("Hour") &":" & rsDC1("Minute") & ", " & rsDC1("Value") & "; "
rsDC1.MoveNext
Loop
rsDC1.Close
Set rsDC1 = Nothing
DataConn.Close
Set DataConn = Nothing
Response.write GamArray
Next %>

 

[vicvirk]

no idea why, but for starters, your db connection string should be outside of the for loop

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "DATABASE=runtime;DSN=WEBSQL;UID=wwuser;Password=wwuser"
For Counter
...
next
dataConn.close
set dataConn = nothing

There is no point in closing it then re-opening it on the very next command.

TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.

 

[BigRed1212]

What vic said.

Open the connection.

Do your bit loop around.

Close the connection.

If you want to split stuff and mess around and you have a lot to do or a big recordset you might want to think about getrows. You already are doing an array.