[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3009
  • Last Modified:

SqlDataReader and sorting


I have a page with an html table, populated with an SqlDataReader. I would now like to be able to click the column headers to sort the rows by that column. I expect I will add onclick events to the table headers, calling a javascript function sortByCol(column,ascending) that does.... what? How do I get the page to reload with the SqlDataReader sorted the way I want?

In my Page_Load, there is a SELECT which loads the SqlDataReader:

"SELECT * FROM DoneDeal, DealType WHERE DoneDeal.DealTypeID = DealType.ID ORDER BY DoneDeal.targetName"

So, I have to reload the page with parameters (from my JS function sortByCol) e.g.

mypage.aspx?sort=2&ascending=0

abd then change my ORDER BY clause. How is this solution? But, how do I access 'sort' and 'ascending' values from the URL in Page_Load?
0
RichardFox
Asked:
RichardFox
  • 8
  • 5
  • 3
  • +1
3 Solutions
 
Anthony PerkinsCommented:
>>How do I get the page to reload with the SqlDataReader sorted the way I want?<<
You will need to requery your database with the right ORDER BY clause.
0
 
HavaganCommented:
Out of curiousity, why not use a DataGrid, with built-in sorting, instead of an HTML table?

Paul
0
 
raterusCommented:
Yes, I'd like to ask that as well, No DataGrid?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RichardFoxAuthor Commented:
No DataGrid, just:

public class Deals : System.Web.UI.Page
{
      public SqlDataReader oDR = null;
      private SqlConnection oConn = null;
      private SqlCommand oCmd = null;

      private void Page_Load(object sender, System.EventArgs e)
      {
            //open db connection & get deal listing
            string sqlDeals = "SELECT * FROM DoneDeal, DealType WHERE DoneDeal.DealTypeID = DealType.ID ORDER BY DoneDeal.targetName";                  
            oConn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
            oConn.Open();
            if(oConn.State == ConnectionState.Open)
            {
                  Page.Trace.Write("TEST","Database connection successful!");
                  oCmd = new SqlCommand(sqlDeals, oConn);
                  oCmd.CommandType = CommandType.Text;
                  oDR = oCmd.ExecuteReader();
            }
            else
            {
                  Page.Trace.Write("TEST", "Database connection failed!");
            }

I get the rows with

<%
int iRowCount = 0;
while(oDR.Read())
{
      iRowCount++;
      DateTime dt = DateTime.Parse(oDR["Announced"].ToString());
      %>
      <!-- Replace("'","\'") changed 11/17/04 RF -->
      <TR onClick="openDeal('<%=oDR["DoneDealID"]%>','<%=oDR["TargetName"].ToString().Replace("'","")%>');" onMouseOver="this.className='rowHover';" onMouseOut="this.className='<%=(iRowCount%2==1?"RowA":"RowB")%>';" CLASS="<%=(iRowCount%2==1?"RowA":"RowB")%>">
            <TD><B><%=oDR["TargetName"]%></B> (<%=oDR["TargetCity"]%>, <%=oDR["TargetState"]%>)</TD>
            <TD><%=oDR["Type"]%></TD>
            <TD ALIGN="center"><%=oDR["TargetEBITDA"]%></TD>
            <TD ALIGN="center"><%=oDR["TargetRevenue"]%></TD>
            <TD ALIGN="center"><%=dt.ToString("MM/dd/yy")%></TD>
      </TR>
      <TR><TD COLSPAN="5" CLASS="RowDivider"></TD></TR>
      <%
}
%>

I'll check out the DataGrid, I have never used one...
0
 
RichardFoxAuthor Commented:

OK, now I remember. I used a datagrid in my first database web page  page, but what I didn't like about it was the look and feel. HTML tables are much more flexible and you can make them look and behave very nicely. I could not find a way to customize the DataGrid

Rich
0
 
Anthony PerkinsCommented:
>>I could not find a way to customize the DataGrid<<
Than I suggest you look into it more.  See here:
An Extensive Examination of the DataGrid Web Control
http://aspnet.4guysfromrolla.com/articles/040502-1.aspx
0
 
raterusCommented:
The datagrid is about the MOST customizable control asp.net has.  I'd really encourage you to look more into it, and if you have questions, or you think you can't do something, just ask us, we'll be sure to help!

--Michael
0
 
RichardFoxAuthor Commented:
what is this GridView control I am reading about? It is not in my .NET Framework 1.1.... Is it better than DataGrid?
0
 
Anthony PerkinsCommented:
That is because it is in ASP.NET 2.0, see here:
Move Over DataGrid, There's a New Grid in Town!
http://msdn.microsoft.com/msdnmag/issues/04/08/GridView/default.aspx
0
 
RichardFoxAuthor Commented:

OK, back to the DataGrid because GridView is not here yet

If I put a DataGrid on my page, with

<ASP:DATAGRID RUNAT="server" ID="oDG"> </ASP:DATAGRID>

I do not seem to be able to refer to this control from my Page_Load in my codebehind file, e.g. oDG.DataBind(). Isn't the control already instantiated with Page_Load is called in the page's global space?
0
 
Anthony PerkinsCommented:
You really ought to take a look at the link I posted earlier on.  Here it is again:

An Extensive Examination of the DataGrid Web Control
http://aspnet.4guysfromrolla.com/articles/040502-1.aspx
0
 
RichardFoxAuthor Commented:
Yeah, I did. And it's a great article. But he gives his examples with the code all in one page. I have a .aspx pagte, and then the aspx.cs page. And I would like to refer to the DataGrid control, which I ID'd as 'oDG', from my Page_Load function. But, my Page_Load function does not recognize the oDG object, which is defined with a tag in my aspx page. I already have a Page_Load funciton which looks like

public class Deals : System.Web.UI.Page
{
      public SqlDataReader oDR = null;
      private SqlConnection oConn = null;
      protected System.Web.UI.WebControls.DataGrid oDG;
      private SqlCommand oCmd = null;


   private void Page_Load(object sender, System.EventArgs e)
   {
      //open db connection & get deal listing
      string sqlDeals = "SELECT * FROM DoneDeal, DealType WHERE DoneDeal.DealTypeID = DealType.ID ORDER BY DoneDeal.targetName";                  
      oConn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
      oConn.Open();
      if(oConn.State == ConnectionState.Open)
      {
            Page.Trace.Write("TEST","Database connection successful!");
            oCmd = new SqlCommand(sqlDeals, oConn);
            oCmd.CommandType = CommandType.Text;
            oDR = oCmd.ExecuteReader();
            //Page.Trace.Write("TEST",(oDR==null?"NULL":"not NULL"));
      }
      else
      {
            Page.Trace.Write("TEST", "Database connection failed!");
      }
   }

}

And I would like to integrate the DataGrid binding here.
0
 
RichardFoxAuthor Commented:
I hope I made my (current) question clear, how to refer to an asp tag object from my codebehind page. After this I'll close this question and start separate questions on DataGrid as needed.
- rich
0
 
RichardFoxAuthor Commented:


BTW, I do not think a DataGrid has built-in sorting, as Havagan said. You have to write a sort function and generate the correct ORDER BY clause whether you use a DataGrid or build a table by hand.
0
 
Anthony PerkinsCommented:
That is covered in Part 4 of the link I posted.
0
 
RichardFoxAuthor Commented:
Yes, but the whole point of switching to the DataGrid would be if it made my life easier. But in fact, if I have to write the sort function anyway, there is less incentive to switch from my already written beautiful HTML table, fed by an SqlDataReader, to a DataGrid. Can you name any advantages, other than the DataGrid's encapsulated nature, which is I have to say very nice.
0
 
raterusCommented:
You are likely to get better help if you choose the datagrid, as the experts here and abroad are familiar with it.  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now