none
SqlDataReader sorting using DataTable /DataView RRS feed

  • Question

  • Hey everyone,

     

    I am trying to sort through a ASP GridView whose DataSource has been set using a SqlDataReader.

     

    I would now like to sort the GridView. My current approach has been to load a DataTable with a the GetSchemaTable from my SqlDataRead. Then load my DataTable into a DataView object and then sort.

     

    However, i get the error IndexOutOfRange Exception, Cannot find column Menu_ID. this implies my DataTable is in fact not filled with the data.

     

    Can anyone point me in the right direction please?

     

    Many Thanks,

    James

     

    P.S i have omitted some of the earlier code as i dont think it is related. I close one datareader and open another due to the fact my pointer is at the end of the first.

     

    //Set primary key of DataGrid

    String[] DataKeys = new String[1];

    DataKeys[0] = "Menu_ID";

    GridView1.DataKeyNames = DataKeys;

    //Add select command field column

    CommandField cf = new CommandField();

    cf.ButtonType = ButtonType.Image;

    cf.SelectImageUrl = "~/Images/ViewHor.jpg";

    cf.ShowSelectButton = true;

    GridView1.Columns.Add(cf);

    //set source to datareader and then bind.

    sdr.Close();

    //re-open datareader

    SqlDataReader sd = oData.OpenDataReader(Stored_Proc_Name, new SqlParameter("@Mode", Stored_Proc));

    GridView1.DataSource = sd;

    dt = new DataTable();

    dt = sd.GetSchemaTable();

     

    GridView1.DataBind();

    sd.Close();

    GridView1.AllowSorting = true;

    }

     

    protected void GridView1_OnSorting(object sender, GridViewSortEventArgs e)

    {

    if (dt != null)

    {

    DataView dv = new DataView(dt);

    dv.Sort = e.SortExpression + " " + Sort_Direction_Convert(e.SortDirection);

    }

    }

    protected String Sort_Direction_Convert(SortDirection Sort_Direction)

    {

    String newSortDirection = String.Empty;

    switch (Sort_Direction)

    {

    case SortDirection.Ascending:

    newSortDirection = "ASC";

    break;

    case SortDirection.Descending:

    newSortDirection = "DESC";

    break;

    }

    return newSortDirection;

    }

    Thursday, August 9, 2007 1:19 PM

Answers

  • Hi,

     

    Why not just use an SQLDataAdapter instead of an sqlreader? With SqlDataAdapter, you can just call fill and set your select statement to the stored procedure that you are using. With this you can directly set the datatable as your GridViews datasource.

     

     

     

    cheers,

     

    Paul June A. Domag

    Thursday, August 9, 2007 2:35 PM

All replies

  • Hi,

     

    Why not just use an SQLDataAdapter instead of an sqlreader? With SqlDataAdapter, you can just call fill and set your select statement to the stored procedure that you are using. With this you can directly set the datatable as your GridViews datasource.

     

     

     

    cheers,

     

    Paul June A. Domag

    Thursday, August 9, 2007 2:35 PM
  • Thank you for the advice. Have not worked with DataAdapters before so i will have a look at using it.

     

    Many Thanks,

    James

    Thursday, August 9, 2007 3:21 PM