Dynamic sort for a GridView

Here is a sample to pass column variables to control SORT ORDER:

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”  
            DataSourceID=”SqlDataSource1″> 
            <Columns> 
                <asp:BoundField DataField=”Item” HeaderText=”Item” SortExpression=”Item” /> 
                <asp:BoundField DataField=”Category” HeaderText=”Category”  
                    SortExpression=”Category” /> 
                <asp:BoundField DataField=”id” HeaderText=”id” SortExpression=”id” /> 
            </Columns> 
        </asp:GridView> 
        <asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”  
            ConnectionString=”<%$ ConnectionStrings:mytest1ConnectionString %>” > 
           
        </asp:SqlDataSource> 
        <asp:Button ID=”Button1″ runat=”server” onclick=”Button1_Click” Text=”Sort” /> 
         <asp:Button ID=”Button2″ runat=”server” onclick=”Button2_Click” Text=”ReverseSort” /> 
 
 
protected void Page_Load(object sender, EventArgs e) 
        { 
            if (!IsPostBack) 
            { 
                SqlDataSource1.SelectCommand = “SELECT id, Item, Category FROM Items order by id desc”; 
                GridView1.DataBind(); 
            } 
        } 
 
        protected void Button1_Click(object sender, EventArgs e) 
        { 
            string sortBySelected = “Item”;  
            string sortBySelected2 = “Category”; 
           
            SqlDataSource1.SelectCommand = “SELECT id,Item, Category FROM Items ORDER BY CASE WHEN @sortBySelected = ‘Item’ THEN Item END, CASE WHEN @sortBySelected2 = ‘Category’ THEN Category END”; 
            SqlDataSource1.SelectParameters.Clear(); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected”, sortBySelected); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected2”, sortBySelected2); 
                       
            GridView1.DataBind(); 
        } 
 
        protected void Button2_Click(object sender, EventArgs e) 
        { 
            string sortBySelected = “Item”; 
            string sortBySelected2 = “Category”; 
 
            SqlDataSource1.SelectCommand = “SELECT id,Item, Category FROM Items ORDER BY CASE WHEN @sortBySelected = ‘Item’ THEN Item END DESC, CASE WHEN @sortBySelected2 = ‘Category’ THEN Category END DESC”; 
            SqlDataSource1.SelectParameters.Clear(); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected”, sortBySelected); 
            SqlDataSource1.SelectParameters.Add(“sortBySelected2”, sortBySelected2); 
 
        } 
    }

Advertisements

How to Determine What Fiscal Year of Today Datein ASP.NET?

Here is a code snippet I posted early:

protected void Page_Load(object sender, EventArgs e)

{

int fYear;

if (DateTime.Today.Month < 7)

fYear = DateTime.Today.Year;

else

fYear = DateTime.Today.Year + 1;

Response.Write( “Current fisical year:”+fYear.ToString().Remove(0,2));

}


Format string with GridView

One user at ASP.NEt forum asked a question for how to format a 15 digit number(string) with two hyphen (-) inside at position 10 and 15.

It can be don easily in SQL:

SELECT  LEFT(@From,9) +’-‘+SUBSTRING(@From,10,5)+’-‘+RIGHT(@From,1)

The same can be done at the front end with string manipulation.

For the data column in GridView, we need to convert the BoundField to TemplateField first and apply the string.Insert to Eval at the designated position twice.

Text='<%# Eval(“rawCol”).ToString().Insert(9,”-“).Insert(15,”-“) %>’

We can see this in action below:

<asp:TemplateField HeaderText=”rawCol” SortExpression=”rawCol”>

<EditItemTemplate>

<asp:TextBox ID=”TextBox1″ runat=”server” Text='<%# Bind(“rawCol”) %>’></asp:TextBox>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID=”Label1″ runat=”server” Text='<%# Eval(“rawCol”).ToString().Insert(9,”-“).Insert(15,”-“) %>’></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField=”col” HeaderText=”col” ReadOnly=”True”

SortExpression=”col” />

 


Regular Expression for a fixed length alphanumeric code in ASP.NET

 A length of 7 literal  starts with a(or A) with the third location with a letter(a-f or A-F) plus 4 more numbers.

ValidationExpression

=”^[a|A]+[0-9]+[0-9A-Fa-f]+\d{4}$”
 
 

 


Inject Javascript from ASP.NET page

There are a few ways to inject script to an ASP.NET page. Where to inject your script is depending on your need of the script on your page.

I am listing three ways to do it for one of my recent task:

1. If you want a startup type of javascript, you can use this syntax to inject the script to the end of the page just before the closing Form tag, you can use RegisterStartupScript:

 Page.ClientScript.RegisterStartupScript( Me.[GetType](), “myScript”, sbOfTheScript.ToString())

2. If you want the script goes to the top of the page just after the  Form tag, you can use RegisterClientScriptBlock:

 Page.ClientScript.RegisterClientScriptBlock( Me.[GetType](), “myScript2”, sbOfTheScript.ToString())

3. If you want the script to be injected inside the Header part of your page, you can use a HtmlGenericControl to load a literal control for the script (Assume your Head tag’s id is “Head” and with a runat=”server” mark up):

  Dim head As HtmlGenericControl = DirectCast(FindControl(“Head”), HtmlGenericControl)
            head.Controls.Add(New LiteralControl(strOfYourScriptCode))


” hide a gridview column if all the row values for that column are null”

Here is a code snippet I posted  before:

Here is working sample from a previous thread:

1.Add these two events to the gridview

OnRowCreated=”GridView1_OnRowCreated” 
OnDataBound=”GridView1_DataBound”   

2.In the code behind:

C#:

Boolean[] notNulls;
 
    protected void Page_Load(object sender, EventArgs e)
    {
      int colCount = GridView1.Columns.Count;
        notNulls = new Boolean[colCount];
       
        for (int i = 0; i < colCount; i++)
        {
            notNulls[i]=false;
        }
    }
    protected void GridView1_DataBound(object sender, EventArgs e)
    {
        for (int cellNum = GridView1.Columns.Count – 1; cellNum >= 0; cellNum–)
        {
            for (int i = 0; i < notNulls.Length; i++)
            {
                Boolean myLocalBool = notNulls[i];
                if (myLocalBool == false)
                {               
                    GridView1.Columns[i].Visible = false;
                   
                }
            }

        } 
    }

    protected void GridView1_OnRowCreated(object sender, GridViewRowEventArgs e)
    {
       
        if (e.Row.RowType == DataControlRowType.DataRow)
        {

            DataRowView drv = (DataRowView)e.Row.DataItem;

            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (!(drv[i] == DBNull.Value))
                {
                    notNulls[i] = true;
                   
                }
              
            }

        }          

    }

VB.NET:

Dim notNulls As Boolean()

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim colCount As Integer = GridView1.Columns.Count
        notNulls = New Boolean(colCount) {}

        Dim i As Integer = 0
        While i < colCount
            notNulls(i) = False
            i = i + 1
        End While

    End Sub
    Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As EventArgs)
        Dim cellNum As Integer = GridView1.Columns.Count – 1
        While cellNum > 0
            Dim i As Integer = 0

            While i < GridView1.Columns.Count ‘Changed from notNulls.Length in C#

                Dim myLocalBool As Boolean = notNulls(i)
                If myLocalBool = False Then
                    GridView1.Columns(i).Visible = False

                End If
                i = i + 1

            End While
            cellNum = cellNum – 1

        End While
    End Sub
    Protected Sub GridView1_OnRowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim drv As DataRowView = CType(e.Row.DataItem, DataRowView)
            Dim i As Integer = 0

            While i < GridView1.Columns.Count

                If Not drv(i) Is DBNull.Value Then

                    notNulls(i) = True

                End If

                i = i + 1
              
            End While
        End If
    End Sub


Code sample: Use parameter with SqlDataAdapter

 
 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim myPara As String = Request.QueryString("ThreadID")

If (String.IsNullOrEmpty(Request.QueryString("ThreadID"))) Then

myPara = 123

‘"defaultValue"

End If

Dim strConn As String = System.Configuration.ConfigurationManager.ConnectionStrings("myTestConnectionString08").ToString()

Dim myConnection As SqlConnection = New SqlConnection(strConn)

 

Dim myAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Posts WHERE ThreadID=@ThreadID", myConnection)

‘Use parameter with SqlDataAdapter

myAdapter.SelectCommand.Parameters.AddWithValue(

"@ThreadID", myPara)

Dim ds As DataSet = New DataSet()

myAdapter.Fill(ds)

DataList1.DataSource = ds

DataList1.DataBind()

End Sub