Dynamic sort for a GridView

Posted: August 11, 2011 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

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);

}
}

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

Posted: March 21, 2011 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

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

Posted: March 21, 2011 | Author: Jingyang Li | Filed under: ASP.NET 2, SQL Server | Leave a comment

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”>

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

</EditItemTemplate>

<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

Posted: November 17, 2010 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

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

Posted: November 5, 2010 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

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”

Posted: October 10, 2010 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

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

Posted: September 30, 2010 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

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

Change parameter name with datacontrol event without using ParameterPrefix

Posted: February 24, 2010 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

You can pass the original parameter value to your new parameter in your -ING event (here is DELETING) of your SqlDataSource.

You can use the same logic to do update.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnDeleting="SqlDataSource1_Deleting" ….

Code:

protected void SqlDataSource1_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{

e.Command.Parameters["@pPartNumberID"].Value = e.Command.Parameters["@PartNumberID"].Value;

//Remove Parameter @PartNumberID
e.Command.Parameters.Remove(e.Command.Parameters["@PartNumberID"]);

}

Use a Pipe delemited file in ASP.NET

Posted: January 30, 2010 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

It is very straightforward to use CSV delimted with comma or tab, since they are standard default setting in registry under this entry:HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text

You may need to set to other values to use other delemiters delimited files. When you import a pipe delemited file, you can change the delemiter to pipe. But you cannot work with other type deleimeted files. Another way is to use Schema.ini file to control a specific text file for importing.

One way to define the delimiter: you can use a Schema.ini file to define your text file name and delimiter.

Here is a sample Schema.ini file and it will sit in the same folder as your text file.:

[myPipeFileName.txt]
Format=Delimited(|)
ColNameHeader=False
MaxScanRows=0

More information about Schema.ini: http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Here is an example with a button clik to show a piple delemited on an ASP.NEt page:

protected void Button1_Click(object sender, EventArgs e)

{

string str = System.IO.Path.GetDirectoryName(MapPath(".\\App_Data\\myPipe.txt"));

string strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + str + "; Extended Properties=\"Text;HDR=No;\"";

OleDbConnection oCon = new OleDbConnection(strConString);

oCon.Open();

//F1, F2, F3 …. in the place of column names

string strSql = "SELECT * FROM myPipe.txt";

OleDbDataAdapter daSource = new OleDbDataAdapter(strSql, oCon);

DataSet ds = new DataSet();

daSource.AcceptChangesDuringFill =

false;

daSource.Fill(ds,

"TransferData");

GridView3.DataSource = ds;

GridView3.DataBind();

}

Radiobuttonlist with a tooltip context from database

Posted: December 4, 2009 | Author: Jingyang Li | Filed under: ASP.NET 2 | Leave a comment

I came across this request and solved with a solution by adding listitems from code and add Titile attribute to the listitems for the tooltip content.

(http://forums.asp.net/t/1487353.aspx)

The forum user came back one month later with extra but I cannot make it work with the internal property tweak. With the clue from the user research, I continued with the search and found another solution with CSS style. I dropped the title property solution with a hyperlink which includes listitems and contents for tooltips.

The style I grabbed from this link: http://jlhaslip.trap17.com/samples/tooltips/index.html

Finally, I came up with the solution for the new requirement from this thread: http://forums.asp.net/p/1500095/3546469.aspx#3546469

I post the answer here for future reference:

Here is a link of the style you will use for your tooltips:

http://jlhaslip.trap17.com/samples/tooltips/index.html

Here is the CSS style from the link:

text/css">
 /*
        =================================
        start of Tooltip css code here
        ================================= */
 
        a.info{
        position:relative;           /*this is the key*/
        z-index:24;
        background-color:#e0e0e0;    /* background colour of display text */
        color:#000000;               /* colour of display text */
        border:1px dotted #999;    /* border colour */
        text-decoration:none;
        font-style:italic;
        }
 
        a.info:hover {
        z-index:25;
        background-color:#ffff66;
 
        }
 
        a.info span{
        display: none;  /* hide the span text using this css */
        }
 
        a.info:hover span{ /*the span will display just on :hover state*/
        display:block;
        position:absolute;
        top: 1.5em;
        left: 3em;
        width:15em;
        border:1px solid #ff0000; /* border colour */
        background-color:#ffff99; /* background colour here */
        color:#000000;         /* text colour */
        text-align: center;
        font-size: .8em;
        font-style:italic;
        z-index:30;
        }

You need to add a hyperlink and define a tag for the tooltip text of your RadiobuttonList in your code:

string myLink = "<a href=\"#\" class=\"info\">" + rblReader_ChangeTypes["Name"].ToString() + ""+rblReader_ChangeTypes["ToolTip"].ToString()+"</a>"

;

The code block:

string connectionString = ConfigurationManager.ConnectionStrings["forumConnectionString"].ConnectionString;

SqlConnection sqlConn1 = new SqlConnection(connectionString);

SqlCommand sqlCmd_GetChangeTypes = new SqlCommand("Select ID, Name, tooltip FROM [aTable]", sqlConn1);

sqlConn1.Open();

SqlDataReader rblReader_ChangeTypes = sqlCmd_GetChangeTypes.ExecuteReader();

if (rblReader_ChangeTypes.HasRows)

{

while (rblReader_ChangeTypes.Read())

{

string myLink = "<a href=\"#\" class=\"info\">" + rblReader_ChangeTypes["Name"].ToString() + ""+rblReader_ChangeTypes["ToolTip"].ToString()+"</a>";

ListItem li = new ListItem(myLink, rblReader_ChangeTypes["ID"].ToString());

//li.Attributes.Add("title", rblReader_ChangeTypes["ToolTip"].ToString());

//li.Attributes.Add("class", "info");

li.Attributes.Add(

"ID", rblReader_ChangeTypes["ID"].ToString());

rblChangeType.Items.Add(li);

//Source for Tooltip

// http://jlhaslip.trap17.com/samples/tooltips/index.html

}

rblChangeType.SelectedIndex = 0;

}

else

{

// logic for no rows had been returned

}

rblReader_ChangeTypes.Close();

sqlConn1.Close();

Jingyang Li

SQL Server