Stuck on Code error

[obuspider]

Can someone look at this and tell me what I'm missing. It's been a while since I've worked in ASP. Eventually, I want to loop through and add up my averages, but I'm getting stuck here.

I'm getting the following error
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

<%
Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot

sConn = &quot;uid=h355;pwd=ga1446;dsn=tracking&quot;

oConn.ConnectionTimeout=720
oConn.CommandTimeout=180

oConn.Open sConn

dim oRs
set oRs = Server.CreateObject(&quot;ADODB.Recordset&quot
oRs.Open
'oRs.ActiveConnection = oConn


s_date = &quot;10/1/02&quot;
e_date = &quot;10/7/02&quot;
txtarea = &quot;13W&quot;

x = dateDiff(&quot;d&quot;,cdate(s_date),cdate(e_date))
'response.write x
for i = 0 to x

nextday = dateAdd(&quot;d&quot;,i,cdate(s_date))
'response.write nextday & &quot;<br>&quot;
sqlQry1=&quot;Select site, schools.area, schcat, schoolname, avg(total_enrollment) as enrollment &quot;
sqlQry1 = sqlQry1 & &quot;from (enrollment inner join tbl_scanned_sales on &quot;
sqlQry1 = sqlQry1 & &quot;enrollment.cus_site = tbl_scanned_sales.sc_site) inner join schools &quot;
sqlQry1 = sqlQry1 & &quot;on tbl_scanned_sales.sc_site = schools.site where &quot;
sqlQry1 = sqlQry1 & &quot;sc_sales_date ='&quot; & nextday & &quot;'&quot;
sqlQry1 = sqlQry1 & &quot; and schools.area='&quot; & txtArea & &quot;
sqlQry1 = sqlQry1 & &quot; group by schcat, schools.area, sc_site, schoolname order by schoolname;&quot;

response.write sqlQry1 & &quot;<br><br>&quot;
response.write oRs(&quot;enrollment&quot
next

set oConn = nothing

set ors=nothing

%>

 

[MrGreed]

Try:

Your old code: sConn = &quot;uid=h355;pwd=ga1446;dsn=tracking&quot;

New code sConn = &quot;dsn=tracking;uid=h355;pwd=ga1446;&quot;

Place the dsn connection first in the string.

Your loop code and stuff needs some work too, unless you've not really developed it yet.

&quot;did you just say Minkey?, yes that's what I said.&quot;

MrGreed

 

[GabeC]

It looks like your problem is in the following code:

dim oRs
set oRs = Server.CreateObject("ADODB.Recordset")   
oRs.Open
'oRs.ActiveConnection = oConn

You need to set the ActiveConnection before you Open the recordset.

You also need to supply a SQL statement to open the recordset with. After you build your SQL Statement:

oRS.Open sqlQry1
response.write oRs("enrollment")
oRS.Close
next

You should also destroy your recordset before your connection.

Hope this helps,

Gabe