Array looping problem

[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 %>

 

[markdmac]

Give this a try:

<% 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
 
Do While Not rsDC1.EOF
rsDC1.MoveFirst
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 %>

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[PHV]

I'd create the connection before looping:

<% Dim GamArray
GamArray= Split(Gam,",")
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.CommandType = 1
Dim Counter
For Counter=LBound(GamArray) to UBound(GamArray)
  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"
  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
Next
Set cmdDC = Nothing
DataConn.Close
Set DataConn = Nothing
Response.write Graph1
%>

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ejbauer]

OK. I tried putting the data connection outside of the loop. This did not help. This data is not the only data in the loop. I am making a graph with a chart server for each time the data is looped. If I have 4 graphs that need to be created that works. However I am not getting my data line because for some reason it seems as though the query is not being ran more than the first time through.

If it would help I could post the entire loop?

 

[pellet]

Please post the entire loop so we can see exactly what is happening.