Solved

Datareader does not read data all the time.

Posted on 2008-10-20
11
365 Views
Last Modified: 2013-12-17
I have a method that takes in a index off a selected index of a gridview and reads a record off a table in a sql server db.  The command object uses a stored procedure.  The stored procedure works as expected when executed on the db.  

The data reader will work as expected if you run the method enough times.  The problem is it takes two or more runs of the method to get the correct data.  Typically, the first time you run the method, the datareader comes back with a blank datarow.  The second or more time it will come back with the expected data.

I am using ASP.net with ajax.  I don't think it is a problem with the ajax bedause the code will run through on every click, it's just the datareader that does not work.
private void ShowIssue(int intRecord)
    {
        SqlCommand comGetTicket = new SqlCommand("sp_GetHelpDeskIssuesByID", SqlConn);
        comGetTicket.CommandType = CommandType.StoredProcedure;
        comGetTicket.Parameters.AddWithValue("@IssueID", intRecord);
 
        //Use dataset to get data
        try
        {
            SqlConn.Open();
            SqlDataReader reader = comGetTicket.ExecuteReader();
            while (reader.Read())
            {
                this.pnlMessage.Visible = false;
                this.lblMessage.Text = "";
                this.ddlEmployee.SelectedValue = reader["EmployeeID"].ToString();
                this.ddlDepartments.SelectedValue = reader["DepartmentID"].ToString();
                this.ddlType.SelectedValue = reader["TypeID"].ToString();
                
...
            }
            this.btnSave.Text = "Update Ticket";
        }
        catch (Exception ex)
        {
            ErrHandler.WriteError(ex.Message);
        }
        finally
        {
            SqlConn.Close();
        }
    }

Open in new window

0
Comment
Question by:CW596
  • 7
  • 4
11 Comments
 

Author Comment

by:CW596
ID: 22759911
Not so sure this is an easy problem so I am setting points at 450.
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22759925
How do you call this method?
How does your Page_Load event handler look like?
0
 

Author Comment

by:CW596
ID: 22759932
On the development workstation the failed read from the reader is more of a problem.  When I deploy to the test server, there is still a miss read the first time running the method, but it usually gets to data row correct the second time.  I don't know what significance this brings.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 22

Expert Comment

by:prairiedog
ID: 22759965
Could please answer my questions first?
0
 

Author Comment

by:CW596
ID: 22760220
On the gridview selected index changed event I send the selecteddatakey.value to the ShowIssue(int intRecord) method.  The datakey is an id number for the Issues table in the db.

SetTicket() is a method called in the page load that sets a group of controls to show no data ie. txtDescription.text = "";  etc.

Once a record is selected, a multiview ActiveViewIndex goes to 1.  This also fails when the datareader fails.  

Sorry about the delay in responding.
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.txtDate.Text = DateTime.Today.ToShortDateString();
            this.adminHelpDeskMultiView.ActiveViewIndex = 0;
            SetTicket();
        }
        
    }

Open in new window

0
 

Author Comment

by:CW596
ID: 22760257
I can step through the code in the ShowIssue(int intRecord).  It's just that the reader has empty data columns the first 2 + times through.  Coding the NextResult() will throw an exception because there should only be on data row retrieved from the stored procedure.
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22761177
1. I don't see anything abnormal in Page_Load.
2. If you don't mind, please post your GridView's SelectedIndexChanged event handler.
3. A side note: always call reader.HasRows before reader.Read()
if (reader.HasRows)
{
   while (reader.Read())
   //....
}
0
 

Author Comment

by:CW596
ID: 22761381
Thanks for the tip about calling HasRows.
protected void gvAllIssues_SelectedIndexChanged(object sender, EventArgs e)
    {
        int intRecord = Convert.ToInt16(this.gvAllIssues.SelectedDataKey.Value);
        ShowIssue(intRecord);
        this.adminHelpDeskMultiView.ActiveViewIndex = 1;
    }

Open in new window

0
 
LVL 22

Accepted Solution

by:
prairiedog earned 450 total points
ID: 22761502
1. I don't see anything wrong with your code.
2. My best guess is the logic for toggling on/off some controls is the possible cause.
3. If the stored procedure returns more than one record, then you are overwriting your controls in the "while" loop. Make sure the store procedure only returns one value.
4. I also noticed that you didn't post the complete code for the "while" loop, you may want to check the rest portion of the code for any incorrect logic that might hide the data.
0
 

Author Comment

by:CW596
ID: 22767205
What I originally thought was a problem with the datareader is actually something else.  One the page I have, events will fire off, however, the code does not get posted back to the client on the first attempt.  

I put a button on the page with a line of code to change a multiview index from 0 to 1.  The first click will step through the code but the view does not change.  When I click the button the second time, the view will change to index 1.
0
 

Author Closing Comment

by:CW596
ID: 31507881
This problem was I had an extra set of UpdatePanel tags I did not delete from changes I made earlier.  If there is a way to provide partial credit for you time I would like to give you 50 for your help.  It got me asking different questions leading to the problem.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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