Please go easy on me - I'm very new to .net development (come from classic asp background).
I have is 2 tables GROUPS and QUESTIONS and what I want to do is return the data from Groups and create a query against QUETSIONS - based on the Group_ID.
So, for example I have 2 Groups - GroupA (Group id 1) and GroupB (Group id 2)
I then have 4 Questions - Question 1 & Question 2 (both with Group ID 1) and Question 3 & Question 4 (both with Group ID 2).
The 2 SQL commands would be "SELECT * FROM Groups" and then "SELECT * FROM Questions WHERE Group_ID = " + the current Group ID value from the first query.
Now I've tried my best to get this to work, but with little luck.
I did manage to get it working with just the 1st (Group) query, but when I then try and introduce the 2nd (Question) query, I get an error....
"There is already an open DataReader associated with this Command which must be closed first."
Here's my working version which only returns the Groups, not the Questions.
Please could someone advise how to introduce the Questions data?
|
Many Thanks
Gary
Rakesh JhaPosted Oct 5, 2010, 9:29 AM
You can also use following code.
Table tbl = new Table();
SqlDataReader Questions;
using (SqlConnection dbConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString()))
{
SqlDataAdapter ObjDAdapter = new SqlDataAdapter("Select * from QGroup", dbConn);
DataSet ObjDS = new DataSet();
dbConn.Open();
ObjDAdapter.Fill(ObjDS, "QGroup");
dbConn.Close();
for(int i=0; i < ObjDS.Tables[0].Rows.Count; i++)
{
TableRow tr = new TableRow();
tbl.Rows.Add(tr);
TableCell Group = new TableCell();
Group.Text = ObjDS.Tables[0].Rows[i][1].ToString();
Group.Width = Unit.Percentage(100);
tr.Controls.Add(Group);
tr.Dispose();
String sGrpId = String.Empty;
sGrpId = ObjDS.Tables[0].Rows[i][0].ToString();
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = dbConn;
if (dbConn.State == ConnectionState.Open)
dbConn.Close();
cmd2.Connection.Open();
cmd2.CommandText = "Select * from QAnser where GrpID='" + sGrpId + "' Order by QuesId";
Questions = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
if (Questions.HasRows)
{
while (Questions.Read())
{
TableRow ObjTr = new TableRow();
tbl.Rows.Add(ObjTr);
TableCell Answer = new TableCell();
Answer.Text = Questions.GetValue(1).ToString();
Answer.Width = Unit.Percentage(100);
ObjTr.Controls.Add(Answer);
}
}
cmd2.Dispose();
}
}
Pasan RatnayakePosted Oct 5, 2010, 9:42 AM
First of all, the code that I attached is a "Sample" code. Sample code cannot be used directly since I do not know the exact details to code it for you and I don't have your source code to write and test it. I just wanted you to understand the concept behind it.
The whole point is that, if you want to run another query, all you had to do was to create a new "SqlCommand" object, not "SqlConnection". The reason, you have to do that was because, you had a "SqlDataReader" reading data from the database through an active connection which is the "SqlCommand" object. Since it is already used, you have to create another "SqlCommand" object to use or close the "SqlDataReader" object associated with the reader.
If you share the source code, at least the original code behind page in which you got the error, I might be able to fix it up for you and post it.
Hope this helps.
Gary KingPosted Oct 5, 2010, 8:32 AM
I downloaded your code and placed it in the code behind page, but am getting some errors (see attached screenshot).
Regards
Gary
Not sure if the jpeg attachment worked, so here are the errors:
'ASP.default_aspx' does not implement interface member 'System.Web.IHttpHandler.IsReusable'
'ASP.default_aspx.GetTypeHashCode()': no suitable method found to override
'ASP.default_aspx.ProcessRequest(System.Web.HttpContext)': no suitable method found to override
Make sure that the class defined in this code file matches the 'inherits' attribute, and that it extends the correct base class (e.g. Page or UserControl) (Line 18 Col 33)
Pasan RatnayakePosted Oct 5, 2010, 7:44 AM
I just saw this thread and I thought this might help you.
Actually, even though your finding in your last post has solved the problem for you, it is not necessarily so. A single "SqlConnection" can have many "SqlCommand"s associated with it and you can also re-use the same "SqlCommand" object to perform your queries (which is not a good practice).
But there can be only one "SqlDataReader" open for a "SqlCommand" at a given time. You must close the reader by calling the "close()" method or you must create another "SqlCommand" to run your other SQL.
Please, see my attachment and it may help you.
Hope this helps. Regards
Gary KingPosted Oct 5, 2010, 5:16 AM
What I needed to do was create multiple databasde connections - 1 connection per Reader. This is very strange as in classic asp you can have a single db connection against which you can execute as many sql commands (resulting in multiple recordsets) as you like. Why this isn't the case with .net is beyond me.
However, I believe that if you use MARS you can create multiple readers for a single connection, but this only works for SQL 2005 onwards (I am using SQL 2000 and .Net framework 2.0 for this particular project).
Anyway, thanks very much for taking the time to post your advice.
theLizardPosted Oct 4, 2010, 4:30 PM
If the example you posted works for one group and the error is There is already an open DataReader associated with this Command" then the answer is simple, when I looked at the code I saw nothing that really wrong, If you want to get records from a second table or even the same table and maintain the first query set you need to instantiate a second set of Sql????? SqlCommand cmd_group_1, cmd_group_1 etc.
Felipe RamosPosted Oct 4, 2010, 9:12 AM
Gary KingPosted Oct 1, 2010, 4:53 AM
I'm afraid that I'm still having no luck whatsoever - I'm getting errors that I just dont understand. In fact, I cant even figure out the code that you provided.
As I said in my original post, I'm very new to .net and am struggling to "think" in .net
Coming from a classic asp background, this would be simple to accomplish in classic - I'd have an rsGroups recordset which as I look through I'd create an rsQuestions recordset based on the the rsGroups Group ID.
So, I'd get my 1st Group from rsGroups and output as follows:
Response.Write("
Then I would get the Questions that belong to the 1st Group and output:
Response.Write("
Then I move to the next record in rsGroups and repeat until I reach the end of rsGroups
With my lack of knowledge/experience with .net, I think that what I need is code for the complete page including all the using "stuff" at the top, have the data read and output to my
Felipe RamosPosted Sep 30, 2010, 12:57 PM