As you can see GetCategoryScore is nested/inside GetCategory List. I
read that you can't next DataReaders. How do you nest stored procedure
calls then? (Where the child sp receives the outputed value of the
parent sp)
Thank you, I really appreciate your response.
Jim
public static String GetCategoryList(SqlConnection conn, string
TypeID, string Office, string Year)
{
SqlCommand cmd = new SqlCommand("Trust_Select_CategoryList_sp",
conn);
cmd.CommandType = CommandType.StoredProcedure;
if (TypeID != "NULL")
{
SqlParameter cmdTypeID = new SqlParameter("@TypeID",
SqlDbType.VarChar);
cmdTypeID.Direction = ParameterDirection.Input;
cmdTypeID.Value = TypeID;
cmd.Parameters.Add(cmdTypeID);
}
SqlDataReader dr;
dr = cmd.ExecuteReader();
String ScoreGrid = "";
while (dr.Read())
{
ScoreGrid = ScoreGrid + "<tr><td>" + dr["Desc"] + "</td>";
String CategoryScore = DataAccess.GetCategoryScore(conn, Office,
Year, dr["CategoryID"].ToString());
ScoreGrid = ScoreGrid + "<td>" + CategoryScore + "%</td></tr>";
}
dr.Close();
conn.Close();
return ScoreGrid;
}
public static String GetCategoryScore(SqlConnection connCategory,
string OfficeID, string Year, string CategoryID)
{
SqlCommand cmd = new SqlCommand("Trust_Select_CategoryScore_sp",
connCategory);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter cmdOfficeID = new SqlParameter("@OfficeID",
SqlDbType.VarChar);
cmdOfficeID.Direction = ParameterDirection.Input;
SqlParameter cmdYear = new SqlParameter("@Year", SqlDbType.VarChar);
cmdYear.Direction = ParameterDirection.Input;
SqlParameter cmdCategoryID = new SqlParameter("@CategoryID",
SqlDbType.VarChar);
cmdCategoryID.Direction = ParameterDirection.Input;
cmdOfficeID.Value = OfficeID;
cmd.Parameters.Add(cmdOfficeID);
cmdYear.Value = Year;
cmd.Parameters.Add(cmdYear);
cmdCategoryID.Value = CategoryID;
cmd.Parameters.Add(cmdCategoryID);
SqlDataReader drCat;
drCat = cmd.ExecuteReader();
String CategoryScore = "";
while (drCat.Read())
{
CategoryScore = drCat["Category_Score"].ToString();
}
drCat.Close();
connCategory.Close();
connCategory.Dispose();
return CategoryScore;
}
