Solved

Datareader does not read data all the time.

Posted on 2008-10-20
11
361 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 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

13 Experts available now in Live!

Get 1:1 Help Now