CASE in ORDER BY clause

When you use a CASE statement to choose which column to sort in an ORDER BY clause, the datatype of all columns wil be an issue.  Here is a real question from a reader:
http://forums.asp.net/t/1251055.aspx
 
Advertisements

Get columns count for GridView when using AutoGeneratedColumns is true

When you set AutoGeneratedColumns to true, you cannot access the GridView’s columns.count from either OnRowDataBound or OnRowCreated event. However, you can use  e.Row.Controls.Count instead. Here is a sample for how to change color for some columns and header in a GridView with AutoGeneratedColumns set to true along with how to change header text for some columns.
 
VB.NET:

Protected Sub GridView1_OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound

If e.Row.RowType = DataControlRowType.Header Then

e.Row.BackColor = Drawing.Color.Yellow

For Each c As TableCell In e.Row.Cells

If c.Text = "ID" Then

c.Text =

"ID change to 1…"

End If

If c.Text = "ID2" Then

c.Text =

"ID2 change to 2…"

End If

Next

End If

If e.Row.RowType = DataControlRowType.DataRow Then

For i As Integer = 3 To e.Row.Controls.Count – 1

e.Row.Cells(i).BackColor = Drawing.Color.Yellow

Next

End If

End Sub

Or

Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowCreated

‘Same code as above

End Sub

C#:

protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.Header)

{

e.Row.BackColor = System.Drawing.

Color.Yellow;

foreach (TableCell c in e.Row.Cells)

{

if (c.Text == "ID")

{

c.Text =

"ID change to 1…";

}

if (c.Text == "ID2")

{

c.Text =

"ID2 change to 2…";

}

}

}

if (e.Row.RowType == DataControlRowType.DataRow)

{

for (int i = 3; i <= e.Row.Controls.Count – 1; i++)

{

e.Row.Cells[i].BackColor = System.Drawing.

Color.Yellow;

}

}

}

Or

protected void GridView1_RowCreated(object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)

{

//same code as above;

}

 

 

Remove some columns when export from GridView to Excel

In your export routine, you can create an index list for the columns to be removed and use this list to turn these columns’ visible to false before you export them.

 Here is what I use:

 

 

 protected void btnExportExcel_Click(object sender, EventArgs e)
    {
....

        ArrayList ExcludedColumnsIndexList = new ArrayList();
        ExcludedColumnsIndexList.Add("7");//the 8th column
        ExcludedColumnsIndexList.Add("8");//the 9th column

        ExportToExcel(filename, gridView1, ExcludedColumnsIndexList);

    }

  public static void ExportToExcel(string myFile, GridView gv, ArrayList removeColumnslistIndex)

{

 //Columns' index arraylist (removeColumnslistIndex) of GridView gv
                 
   if  (removeColumnslistIndex!=null)
                    {

                        for (int j = 0; j < removeColumnslistIndex.Count; j++)
                        {
                            if (gv.HeaderRow != null)
                            {
                                gv.HeaderRow.Cells[Convert.ToInt32(removeColumnslistIndex[j])].Visible = false; 
                            }
                            
                            for (int i=0; i< gv.Rows.Count;i++)
                            {
                                gv.Rows[i].Cells[Convert.ToInt32(removeColumnslistIndex[j])].Visible = false; 
                            }
                            
                            if (gv.FooterRow != null)
                            {
                                gv.FooterRow.Cells[Convert.ToInt32(removeColumnslistIndex[j])].Visible = false;
                            }

                        }
                   
   }

  //other code omitted here

 

 

}


Don’t show item when the value is NULL in a DetailsView

Sometimes you don’t want to show the item if the datum is null on your DetailsView. You can check the value in your detailsview and change the visiblity of that row which includes the item. Here is the code to show how to do it through databound event:
protected void DetailsView1_OnDataBound(object sender, EventArgs e)
    {
        
        
            if (DetailsView1.CurrentMode == DetailsViewMode.ReadOnly)
            {
                //DetailsViewMode.ReadOnly or DetailsViewMode.Edit 

                System.Data.DataRowView rowView = (System.Data.DataRowView)DetailsView1.DataItem;


                //if (rowView.Row[2].ToString() == "") //use column index 
                if (rowView.Row["nullableCol"].ToString() == "") //use column name
                {

                    DetailsView1.Rows[2].Visible = false;//the column has null values

                }


            }
        } 

 

VB.NET:

 Protected Sub DetailsView1_OnDataBound(ByVal sender As Object, ByVal e As EventArgs) Handles DetailsView1.DataBound

       
        If DetailsView1.CurrentMode = DetailsViewMode.ReadOnly Then
            'DetailsViewMode.ReadOnly or DetailsViewMode.Edit 

            Dim rowView As System.Data.DataRowView = _
                             CType(DetailsView1.DataItem, System.Data.DataRowView)


            If rowView.Row(2).ToString() = "" Then

                DetailsView1.Rows(2).Visible = False

            End If

        End If

    End Sub

 

 

Export to excel through OLE automation

There are a few options other than using OPENROWSET to export  excel. From my research, the OLE Automation is a better option.  Here is a good link to show you how you can use OLE automation for this task with a user Stored Procedure.

The link:  http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/#first