Solved

Looping through two identical DataReaders produces an error

Posted on 2015-01-18
10
109 Views
Last Modified: 2015-01-25
Maybe I'm doing something that's not allowed, or doing it wrong, or maybe there's even a better way to do it, I don't know.  But what I am doing isn't working.  In the code sample below the comments at the top tell what I'm trying to do.  Basically I am creating a temporary table containing every possible combination of participants in a Role Playing group and calculating the interval (in minutes rather than days because it is envisioned that this application will be used in the future for hourly scheduling as well as daily and weekly scheduling) between the date that is being passed to this procedure and the date each combination was last used.

The problem occurs on the line which reads:  int m2 = reader2.GetInt32(0); //ParticipantB

The error that is occurring there says that there is no data contained in this object.  Of course there should be since reader2 is - as you can see - meant to contain an exact duplicate of the data that is contained in reader1.

Any idea what I'm doing wrong?  Thanks in advance for your help.

    protected void BuildCombinations(DateTime CurrentInterval)
    {
        /*
        Function BuildCombinations()
            Delete all records in the COMBINATIONS table
            For each Member (J) of the active Group where Unavailable = 0 && Assigned = 0
                For each Member (K) of the active Group where Unavailable = 0 && Assigned = 0
                    If (J) != (K)
                        In COMBINATIONS table:
                            Set ParticipantA = (J)
                            Set ParticipantB = (K)
                            Set LastTeamed = IntervalStartDate - DateTime LastTeamed (from SCHEDULE table)
        */
        
        int activeGroup = Convert.ToInt32(Session["GroupID"]);
        DeleteAllScheduleCombinations(activeGroup);
   
        string ConnectionString1 = ConfigurationManager.ConnectionStrings["dbCS"].ConnectionString;
        using (SqlConnection con1 = new SqlConnection(ConnectionString1))
        {
            SqlDataReader reader1;
            SqlCommand cmd1 = new SqlCommand("spClientGroupMembersUnteamedGet", con1);
            cmd1.CommandType = System.Data.CommandType.StoredProcedure;
            cmd1.Parameters.AddWithValue("@GroupID", activeGroup);
            con1.Open();
            reader1 = cmd1.ExecuteReader();

            if (reader1.HasRows)
            {
                while (reader1.Read())
                {
                    int m1 = reader1.GetInt32(0); //ParticipantA

                    string ConnectionString2 = ConfigurationManager.ConnectionStrings["dbCS"].ConnectionString;
                    using (SqlConnection con2 = new SqlConnection(ConnectionString2))
                    {
                        SqlDataReader reader2;
                        SqlCommand cmd2 = new SqlCommand("spClientGroupMembersUnteamedGet2", con2);
                        cmd2.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd2.Parameters.AddWithValue("@GroupID", activeGroup);
                        con2.Open();
                        reader2 = cmd2.ExecuteReader();

                        if (reader2.HasRows)
                        {
                            while (reader2.HasRows)
                            {
                                int m2 = reader2.GetInt32(0); //ParticipantB
                                if (m1 != m2)
                                {
                                    int lastTeamed = GetDateLastTeamed(CurrentInterval, m1, m2);
                                    WriteScheduleCombination(activeGroup, m1, m2, lastTeamed);
                                }
                            }
                        }
                        con2.Close();
                    }
                }
            }
            con1.Close();
        }
    }

Open in new window


USE [Scheduler_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SchedulerSA].[spClientGroupMembersUnteamedGet] 
	@GroupID int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT Id 
	FROM ClientGroupMembers
	WHERE GroupID = @GroupID AND Unavailable = 0 AND Assigned = 0
END

Open in new window


USE [Scheduler_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [SchedulerSA].[spClientGroupMembersUnteamedGet2] 
	@GroupID int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT Id 
	FROM ClientGroupMembers
	WHERE GroupID = @GroupID AND Unavailable = 0 AND Assigned = 0
END

Open in new window

0
Comment
Question by:penlandt
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40556954
You cannot put datareader inside while loop unless it index changes.

declare a variable before while and store its value once. This way you can reuse it later.

--------
For instillation,

If event there is no while loop and you want to do:

int m1 = reader1.GetInt32(0); //ParticipantA
int mx = reader1.GetInt32(0); //ParticipantA    <-- this line will produce the same error  you are having now
                                                                                         (it will say no data); datareader reads only once and it reads in forward manner.

To do my example successfully, you shoud do:

int m1 = reader1.GetInt32(0); //ParticipantA
int mx = m1l
0
 
LVL 1

Author Comment

by:penlandt
ID: 40556955
Sorry, I'm confused.  I have only referenced the variable m1 one time, and the error is occurring on the line in which the variable m2 is declared - using a completely separate reader.  What am I missing?
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40556960
Please test:

int m1 = reader1.GetInt32(0); //ParticipantA
int mx = reader1.GetInt32(0); //ParticipantA

and compare it with

int m1 = reader1.GetInt32(0); //ParticipantA
int mx = m1;

to see the point I am making. I am reading it again to make sure.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40556969
Also, it seems

// if (reader1.HasRows)   <-- you can do without this if. Try removing this because the
//            {                                 while below does the same think
                while (reader1.HasRows)    //<-- and change this as shown.  
                {                                                        You had while (reader1.Read())

The way you had, you were reading the datareader once in While(reader1.Read()) and for a second time at m1 = reader1.GetInt32(); hence the error.

BTW, I take back the statement on the using datareader in while loop. It is always used that way. I thought this loop reads the same datareader more than once inside the while loop.
0
 
LVL 1

Author Comment

by:penlandt
ID: 40556994
I apologize if I'm misunderstanding you, however it seems that you're focusing on a section of the code that is not producing any errors.  There are two distinct command objects, two distinct datareaders, and two distinct stored procedures involved here.  It is the nested objects (rather than the outer ones) that are producing the error.

Here is the text of the error message (in the event that this might be helpful):

System.InvalidOperationException was unhandled by user code
  HResult=-2146233079
  Message=Invalid attempt to read when no data is present.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
       at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
       at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
       at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
       at Members_Scheduling.BuildCombinations(DateTime CurrentInterval) in c:\Users\MichaelTodd\Documents\Visual Studio 2013\WebSites\RandomScheduler\Management\Group\Scheduling.aspx.cs:line 597
       at Members_Scheduling.BuildSchedule(DateTime CurrentInterval) in c:\Users\MichaelTodd\Documents\Visual Studio 2013\WebSites\RandomScheduler\Management\Group\Scheduling.aspx.cs:line 292
       at Members_Scheduling.ProcessSchedule() in c:\Users\MichaelTodd\Documents\Visual Studio 2013\WebSites\RandomScheduler\Management\Group\Scheduling.aspx.cs:line 113
       at Members_Scheduling.btnSchedule_Click(Object sender, EventArgs e) in c:\Users\MichaelTodd\Documents\Visual Studio 2013\WebSites\RandomScheduler\Management\Group\Scheduling.aspx.cs:line 60
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException:

Screenshot of error
0
 
LVL 1

Author Comment

by:penlandt
ID: 40557003
I should also point out that reader2 DOES actually contain data, so I don't understand why I'm getting an error saying that it doesn't.

Locals Screenshot
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40557010
With this there will be 7 posts in this thread. Would you consider closing this thread and starting a fresh post to get more attention. Meanwhile, I will be going over the entire thread and post any new and relevant findings.

Mike
0
 
LVL 1

Accepted Solution

by:
penlandt earned 0 total points
ID: 40557025
I found the problem!  Even though I looked through the code several times I missed the fact that I had failed to call the Read() method of reader2 before trying to access the data it contained.  I corrected that and everything works fine now.

if (reader2.HasRows)
                        {
                            while (reader2.HasRows) // <----- Should have been while(reader2.Read())

Open in new window

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40557043
I had tunnel vision on this question because of a similar problem I had with data reader reading twice in another project

Congratulation for finding the error.

Mike
0
 
LVL 1

Author Closing Comment

by:penlandt
ID: 40569031
I found the answer to my own question.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question