Solved

Looping through two identical DataReaders produces an error

Posted on 2015-01-18
10
95 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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:penlandt
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
I found the answer to my own question.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now