Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

Looping through two identical DataReaders produces an error

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
penlandt
Asked:
penlandt
  • 5
  • 5
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
penlandtAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
penlandtAuthor Commented:
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
 
penlandtAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
penlandtAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
penlandtAuthor Commented:
I found the answer to my own question.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now