error 80040e4d - Too many client tasks

[FesterSXS]

Hi,

I have a number of ASP pages that contain nested recordsets like follows. If the recordsets are quite large then I sometimes get an error 80040e4d - Too many client tasks.

This is example code but very typical of the kind of thing I do.

strConnect="DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};DBQ=c:\temp\mydb.mdb;"

Set objOuterRS = Server.CreateObject("ADODB.Recordset")
strOuterSQL = "SELECT * FROM T_Outer"

objOuterRS.Open strOuterSQL, strConnect

While NOT objOuterRS.EOF
  Set objMiddleRS = Server.CreateObject("ADODB.Recordset")
  strMiddleSQL = "SELECT * FROM T_Middle WHERE Outer_ID=" & objOuterRS("Outer_ID")

  objMiddleRS.Open strMiddleSQL, strConnect

  While NOT objMiddleRS.EOF
    Set objInnerRS = Server.CreateObject("ADODB.Recordset")
    strInnerSQL = "SELECT * FROM T_Inner WHERE Outer_ID=" & objOuterRS("Outer_ID") & " AND Middle_ID=" & objMiddleRS("Middle_ID")

    objInnerRS.Open strInnerSQL, strConnect

    While NOT objInnerRS.EOF
      ...EVENTUALLY - SOME CODE HERE...
      objInnerRS.MoveNext
    Wend

    objInnerRS.Close
    objInnerRS = Nothing

    objMiddleRS.MoveNext
  Wend

  objMiddleRS.Close
  Set objMiddleRS = Nothing

  objOuterRS.MoveNext
Wend

objOuterRS.Close
Set objOuterRS = Nothing

I am not sure if my method of connecting to the database is the most efficient and would like some feedback from you guys. Tony

http://www.phoenix-paints.co.uk/portfolio

 

[snowboardr]

Maybe try putting the column names you are getting the data from, instead of using a wild card (*)

SELECT table_name, table_name FROM...

Or upgrade your database to MySQL once you start using mysql or SQL, you will never go back!

http://www.vzio.com/tutorials/mysql_database.asp

Access isnt the best db, it can become corrupt if to many users access it at a time.

- Jason www.vzio.com
ASP WEB DEVELOPMENT

 

[FesterSXS]

cheers I'll look at that.

quick question though - can you have a DSNless connection to a MySQL database?? Tony

http://www.phoenix-paints.co.uk/portfolio

 

[snowboardr]

yes

<%
set conn = createobject(&quot;ADODB.Connection&quot
conn.open = &quot;DRIVER={MySQL ODBC 3.51 Driver};&quot;_
& &quot;SERVER=localhost;&quot;_
& &quot;DATABASE=database_name;&quot;_
& &quot;UID=root;PWD=; OPTION=35;&quot;
set rs = conn.Execute(&quot;SELECT * FROM table_name&quot
%>
www.vzio.com
ASP WEB DEVELOPMENT

 

[FesterSXS]

excellent - I'll definitely try that then - will let you know how I get on Tony

http://www.phoenix-paints.co.uk/portfolio

 

[snowboardr]

Its quite easy, much less head-ache then access. I was scared to switch, I thought a database, like mysql or SQL would be hard, time consuming etc.., but so far MySQL was simple, and havn't done anything in ms SQL yet, but it shouldnt be that hard either.

Jason www.vzio.com
ASP WEB DEVELOPMENT

 

[FesterSXS]

just implemented MySQL on one of my sites, replacing the old Access db. Seems to work well (few minor SQL syntax changes required but then I expected that)

Got a problem tho - I use code similar to the following in various parts of my site to determine the recordCount of a recordset - worked fine with my Access db but I just get a value of -1 now......any ideas??

Set objSubRS = Server.CreateObject("ADODB.Recordset")
strSubSQL = "SELECT * FROM T_Sub WHERE App_ID=" & intApp

objSubRS.Open strSubSQL, strConnect, adOpenStatic

intCount = objSubRS.RecordCount

Tony

http://www.phoenix-paints.co.uk/portfolio

 

[FesterSXS]

Also - can I use the 'Now' value in my SQL statements as I get the following error now:

SELECT * FROM T_Product WHERE Last_Date > Now - 7 ORDER BY ID ASC; 

Microsoft OLE DB Provider for ODBC Drivers error '80040e21' 

ODBC driver does not support the requested properties.

Tony

http://www.phoenix-paints.co.uk/portfolio

 

[Tarwn]

One comment concerning the original setup, was there a reason you split everything into seperate queries? Why not just:

SELECT * FROM T_Outer, T_Middle, T_Inner WHERE T_Outer.Outer_Id = T_Middle.Outer_Id AND T_Middle.Middle_Id = T_Lower.Middle_Id AND T_Lower.Outer_Id = T_Outer.Outer_Id&quot;

The last Outer to lower I added in case your relationship isn't
Outer 1->Many Middle 1->Many Lower

If it is you wouldn't need the third condition.


Concerning your last question: It would probably be safer to do the Now-7 in ASP and concatenate it into your SQL String, that way if Now isn't supported somewhere it won't be an issue because you are doing it before sending the query rather than relying on the database server to handle it.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation