Solved

Datareader does not read data all the time.

Posted on 2008-10-20
11
359 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

21 Experts available now in Live!

Get 1:1 Help Now