I have this problem, it's urgent, hopefully i can get the support i've received in the past!

here's some psuedo code

cn.Open
com = New SQLCommand(SQL, cn)
reader = com.ExecuteReader()
DO WHILE reader.Read()
  ...
  lArticleID = reader.Item("ArticleID")
  sReturnText = GetArticle(cn, lArticleID)
LOOP
'-----------
Function GetArticle(byVal cn, byVal AID)
 Dim myCom AS New SQLCommand("Select * FROM [table] where AID="& AID)
 Dim myReader AS SQLDataReader = myCom.ExecuteReader()

 Do While myReader.Read()
  sText = sText & myReader.Item("LineStuff")
 Loop
 return sText
End Function

there is a problem because the connection is being shared between two datareaders!! how do i do this such that i can loop in the main loop and call functions taht use the same connection to have it's method-specific datareaders do work as well????!!! i get the
exception thrown:
"There is already an open DataReader associated with this Connection which must be closed first. "

Once you have a datareader open on a connection, you can't open another reader.

Why are you attempting to run another reader while processing the first? Are you selecting a subset of records in the function that you are calling from the first reader's loop? It sounds like you need to use a dataset instead and get a pair of tables that are related. You can populate two tables, relate them and save all those extra calls to the database from the function. You can then deal with the data from both tables.

Explain briefly what you are doing and maybe we can provide a suggestion. Tell us what your table structure is and what you want to do with the output.

Peter
------------------------------------------------------
Work smarter, not harder.

hello peter, thanks for the input, let me give you a scenario and maybe this can help explain more so that you can give further input.

Lets say for instance i have 10 Categories for a forum. And i want to loop through the tb_Categories table and find the path from a child category to the root for
4 arbitrary child categories categories and individually find a String representation of the path from the given child category to the root. i am taking the child category,
and then calling a recursive function GetSubCategories() that will return me a string of all the subcategories for any given root category. So for instance,

Lets take an arbitrary category Bill Clinton(CategoryID=11), so i call the fuction GetPathToRoot(nCategoryID), this function will then traverse through the database and return me a string that represents the path from the subcategory to the root in the form of "2,6,11" and this string will represent the categoryID's for the path
Presidents -> Democrat -> Bll Clinton.

' Get the CategoryID that represents Bill.
SQL = "Select CategoryID FROM tb_Categories where Name='Bill Clinton' OR Name='George Bush' OR Name= 'Kerry'"

com = new SQLCommand(SQL, cn)
reader = com.ExecuteReader()

DO While reader.Read()
  nCategoryID = Cint(reader.Item("CategoryID"))
  sText = GetPathToRoot(cn, nCategoryID, "")

  ' Do something with sText like get stuff that pretains to
  ' the category id string 'sText'
Loop
reader.close()

'-- Params:
'- _cn: connection do the database so we don't have
' multiple connecitons
'_catID: represents the child categoryid
'_sBuiltStr: Represents the string we have built thus far
'------------------------------------------
Function GetPathToRoot(ByVal _cn, _CatID, _sBuiltStr)
  Dim Com AS SQLCommand
  Dim Reader AS SqlDataReader
  Dim SQL AS String

  if _sBuiltStr = "" then
   _sBuiltStr = _CatID
  else
    _sBuiltStr = Cstr(_CatID) "," & _sBuiltStr
  end if

  SQL = "SELECT CategoryID FROM tb_Categories WHERE ParentID=" & _CatID
  com = New SQLCommand(SQL, _cn)
  reader = com.ExecuteReader()

  if reader.HasRows then
    _CatID = reader.Item("CategoryID")
    reader.close()
    com = nothing
    return GetPathToRoot(_cn, _CatID, _sBuiltStr)
  else
    reader.close()
    com = nothing
    return _CatID
  end if
End Function
'-------------------
'-------------------
Now the recursion i think is almost close, it's a basic upward traversal, but anyways this will give me the error
that the connection already has a datareader associated with it.

Do you see what i'm trying to do? if not let me know so i can clarify. Thanks alot.

I see now what you are trying to accomplish. I would suggest you do all of this in the database with a stored procedure. Making repeated calls to the database from ADO for this type of thing is going to really hurt your performance. It's not really that hard to do with a SPROC. Try this out...

CREATE PROCEDURE GetFullPath
    @sSearch VARCHAR(100)
AS
    set nocount on
    create table #paths (ParentID INT, Path VARCHAR(1000))
    insert into #paths
        select ParentID, ltrim(str(CategoryID)) from tb_Categories
        where Name LIKE '%'+@sSearch+'%'
            and CategoryID <> ParentID
    while(@@rowcount > 0)
    begin
        update #paths set path=ltrim(str(t1.CategoryID))+','+path, ParentID=t1.ParentID
        from tb_Categories as t1
        where CategoryID = #paths.ParentID
            and CategoryID <> ParentID
    end

    select Path from #paths
    drop table #paths
GO


Assuming that you want the categories numbers to eventually show the text name of the category, you could actually put that into the query as well so you don't have to make any more DB hits. You could select both the id and the category name and then let the page logic parse them apart to extract the text and id separately. Here's how you could add it in by modifying the select and update:

        select ParentID, ltrim(str(CategoryID))+'|'+Name from tb_Categories
and
        update #paths set path=ltrim(str(t1.CategoryID))+'|'+Name+','+path, ParentID=t1.ParentID

This would give you this:

    2|Presidents,6|Democrat,11|Bill Clinton

Then you can break it apart by the comma to get the category, then by the pipe to get the id and name. All with only a single DB call.

You may want to use different characters for the separators. A comma would be likely to appear in your category name values so you could use a more unlikely char like a ~. You could actually use a tab and hard return for the separators. This would greatly decrease the chance of problems particularly if you don't allow those characters into your category names which you most likely don't.

Peter
------------------------------------------------------
Work smarter, not harder.

wow peter, you definately live by the work smarter not harder theme! thanks a bunch, i'm gonna try this out and get back to you via this forum. thanks a bunch.

Flyin

well, the tip you gave me is great, except i cannot get it to work!

do you see anything incorrect with this?

com = New SqlCommand("sp_GetFullpath", objCon)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@nCategoryID", SqlDbType.Int)
com.Parameters("@nCategoryID").Direction = ParameterDirection.Input

com.Parameters("@nCategoryID").Value = lCategoryID
com.Parameters.Add("ParentID", SqlDbType.Int)
com.Parameters("ParentID").Direction = ParameterDirection.Input
com.Parameters("ParentID").Value = 0

com.Parameters.Add("Path", SqlDbType.VarChar, 100)
com.Parameters("Path").Direction = ParameterDirection.Output

com.ExecuteNonQuery()
sSubCategoryDesc = com.Parameters("Path").Value.ToString()

thanks

Flyin

sorry the above code was not what i used, this is the correct one

com = New SqlCommand("sp_GetFullpath", cnAccount)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@nCategoryID", SqlDbType.Int)
com.Parameters("@nCategoryID").Direction = ParameterDirection.Input
com.Parameters("@nCategoryID").Value = 18

com.Parameters.Add("RETURN_VALUE", SqlDbType.VarChar, 1000)
com.Parameters("RETURN_VALUE").Direction = ParameterDirection.ReturnValue

com.ExecuteNonQuery()
sSubCategoryDesc = com.Parameters("RETURN_VALUE").value.ToString()


it doesn't return anything. am i going at the return string correctly?

btw i changed it to accept a categoryid instead of the category description
the new sp looks like this


CREATE PROCEDURE sp_GetFullPath
    @nCategoryID INT
AS
    set nocount on
    create table #paths (ParentID INT, Path VARCHAR(1000))
    insert into #paths
        select ParentID, CategoryID from ju_Category
        where CategoryID = @nCategoryID
            and CategoryID <> ParentID
    while(@@rowcount > 0)
    begin
        update #paths set path=t1.CategoryID+','+path, ParentID=t1.ParentID
        from tb_Categories as t1
        where CategoryID = #paths.ParentID
            and CategoryID <> ParentID
    end

    select Path from #paths
    drop table #paths

GO

have i changed this correctly?

thanks a bunch for your help btw

You don't need a return parameter. The procedure will return you a set of rows. In your case, you modified the query to return you only one row and column (or one value). Use ExecuteScalar to get that single value. You can also shorten up the code for the parameter because an added parameter's direction is "Input" by default.

com = New SqlCommand("sp_GetFullpath", cnAccount)
com.CommandType = CommandType.StoredProcedure
com.Parameters.Add("@nCategoryID", SqlDbType.Int).Value = 18
sSubCategoryDesc = CType(com.ExecuteScalar(), String)

Also, I'd recommend not prefixing your stored procedures with "sp_". Any call to a procedure with the "sp_" prefix causes SQL Server to look thru the master database for the procedure before looking in the database itself. This degrades the performance because it actually causes a call miss due to the missing procedure in master before it's found in your currently connected database. (Click for a more complete explanation.)

Peter
------------------------------------------------------
Work smarter, not harder.

once again thanks a bunch, i'll try that out sometime today!

Hey Peter,

Thanks a bunch for the tip, It works tremendously!
my final copy is as follows

CREATE PROCEDURE sp_GetFullPath
    @CID INT
AS
    set nocount on
    create table #paths (ParentID INT, Path VARCHAR(1000))
    insert into #paths
        select ParentID, CategoryID from ju_Category
        where CategoryID = @CID
            and CategoryID <> ParentID
    while(@@rowcount > 0)
    begin
        update #paths set path= convert(VARCHAR(1000),t1.CategoryID)+','+path, ParentID=t1.ParentID
        from ju_Category as t1
        where CategoryID = #paths.ParentID
            and t1.CategoryID <> t1.ParentID
    end

    select Path from #paths
    drop table #paths
GO

this will return child to root. The next will be to find all children given a root category. Hopefully not too different, we'll see!

thanks again