Gridview not populating while using a While() stru

Question

I am having problems with this grid view. I am populating it with a query. However, it will not populate or even appear if I use a while(reader.Read()) structure. Without the while structure, it works fine. However, I need to access two specific fields. The code is below.

 SqlDataReader myReader;
 try
 {
     using (myConnection)
     {
         myConnection.Open();
         ArrayList arrliGames = new ArrayList();
         myReader = myCommand.ExecuteReader();

         decimal decTicketCost = 0;
         int intTicketCount = 0;

         while (myReader.Read ())
         {
             decTicketCost = Convert .ToDecimal (myReader ["TicketCost"]);
             intTicketCount =Convert .ToInt32 (myReader ["NumTickets"]);
         }

         //Binds listbox
         grdEvents.DataSource = myReader ;
         grdEvents.DataBind();
     }
 }

Answer 1

How about this:

using (myConnection)
{
   myConnection.Open();
   DataSet ds = myCommand.ExecuteDataSet();

   //Binds listbox
   grdEvents.DataSource = ds;
   grdEvents.DataBind();                    
}
ForEach (DataRow dr in ds.tables[0].rows)
    myList.Add(new TicketInfo{TicketCost = Convert.ToDecimal(myReader["TicketCost"]),NumTickets = Convert.ToInt32(myReader["NumTickets"])  });  

Answer 2

I guest you have set datasource to myList instead myReader

grdEvents.DataSource = myList;

Edit: You need to add other column in your list object.

while (myReader .Read ())
{
//myList-- Add other columns you need to display in the gridview
//As I don't know the your Data Reader column, I can't give you exact mylist object

 myList.Add(new TicketInfo{TicketCost = Convert.ToDecimal(myReader["TicketCost"]),NumTickets = Convert.ToInt32(myReader["NumTickets"])  });
}

Answer 3

The SqlDataReader is forward-only. When you first iterate over the rows, there is "nothing left" in it to display afterwards.

I suggest that you use the reader to populate a strongly-typed list in memory, and then bind the GridView to the list instead. Example:

var myList = new List<TicketInfo>();
while (myReader.Read())
{
    myList.Add(new TicketInfo
    {
        TicketCost = Convert.ToDecimal(myReader["TicketCost"]),
        NumTickets = Convert.ToInt32(myReader["NumTickets"])
    });
}
grdEvents.DataSource = myList;
grdEvents.DataBind();

The code example above assumes that you have a class called TicketInfo defined as:

class TicketInfo
{
    public decimal TicketCost { get; set; }
    public int NumTickets { get; set; }
}

If you haven't used generics (such as List<TicketInfo> in the example) before, I suggest you do some reading on the subject.

Answer 4

You could replace

while (myReader .Read ())                        
{                             
decTicketCost  = Convert .ToDecimal (myReader ["TicketCost"]);                            intTicketCount = Convert .ToInt32 (myReader ["NumTickets"]);                             
}                              
//Binds listbox                         
grdEvents.DataSource = myReader ;                         
grdEvents.DataBind();  

to grdEvents.DataSource = myReader ;
grdEvents.DataBind();

And then the gridview for some values

hope this help

Answer 5

create a class with two properties 1. decTicketCost 2. intTicketCount

now in while loop create instance and assign the value to the object properties

and add it in a list.

Finally bind the list.

Answer 6

replace the following line

grdEvents.DataSource = myReader;

with

grdEvents.DataSource = myList;