GridView Sorting Event Handler Called Twice on one OnSorting Event

This Is Not An Excuse, It’s a REASON.
It’s been three and a half years since I coded in VB.NET so while developing/maintaining a VB.NET app. on my current project I have been shamelessly copying cannily re-using a lot of existing code.

The event handlers in this app. all have “Handles” clauses hanging off them so I naively assumed this was required VB syntax. Thus began my latest journey in pain.

Something I Learned At Work Today
Did you know that when a GridView does not have a Data Source Control defined that it does not automatically Sort when you click on the Column Headers ? Of couse you did, Vikram Lahotia. I, however, did not as all my Grids had nice ObjectDataSources or similar.

THIS particular one, however, is bound to a DataTable stored in ViewState (N.B. I did not write this page) , so after I found out why it didn’t Sort automatically, I had to handle the OnSorting event, which I did as you might expect:

asp:GridView ID="TrevTheGridView runat="server" etc
OnSorting="TrevTheGridView_OnSorting
Fields and stuff
/asp:GridView

In addition, due to my VB.NET naivety I also did this in code-behind:

Protected Sub TrevTheGridView_Sorting(ByVal sender as Object, ByVal e as GridViewSortingArgs) Handles trevTheGridView.Sorting
‘Flip The Sort Order
‘Define a DataView on the ViewState datatable
‘Assign SortExpression to DataView
‘Rebind Trev to the DataView.ToTable
End Sub

Notice that Handles clause? It’s redundant. I repeat: It’s redundant. The combination of the Handles gridView.Sorting in code-behind and OnSorting=”sortEventHandler” in Markup meant that the Sorting event handler was called twice which caused my Sort Order to be flipped twice meaning that the GridView Sort Order never changed.

Tremendous. If I want to watch something never change I’ll go live in Delaware or hook up to Richie Benaud’s hairdresser on WebCam.

So get rid of the Handles clause if you only want the event handler firing once. Or the declaration if the Event Handler in Markup.

ReportViewer Website Data Sources Pane Suddenly Shows As Empty

It Was There A Moment Ago

I was happily using the excellent and free ReportViewer add-on Control when suddenly I noticed that the Website Data Sources Panel was blank. This is a Category Orange catastrophe for ReportViewer as it means that you can no longer easily add data items to the report without hacking the rdlc file.

Risking Anaphylactic Shock
I responded to this issue with my normal professional saviour-faire, entering an insane frenzy of clicking every available menu option interspersed with hyperactive Googling and masking my sweaty fear by eating handfuls of peanuts.

Just as I was about to sacrifice my emergency sacred ferret to a graven image of Bill Gates I fluked a gigantic ‘WHAT-THE’ on one of my semi-random Menu options. When selecting Report->DataSources from the Visual Studio 2005 main menu (NB you must be currently editing the rdlc file for this menu item to be visible) I got this error message:

“Cannot compile ajaxtoolkit.dll”.

Now, our Web Project has a reference to ajaxtoolkit.dll in the Bin folder which always throws a Compilation Warning. Could ReportViewer be touchy about this ? So I excluded the offending dll from the Project, recompiled the project et voila,…nothing happened…ONLY KIDDING!!.. WebSite DataSources came flowing in as contentedly as obese Policmen to a Krispy Kreme franchise.

Replicating The Problem
1. Have a bad Reference in your Bin folder e.g. put in the wrong path to a dll
2. Edit any DataSet that currently shows in your WebSite Data Sources Window in Visual Studio XML editor (e.g add a new column to a DataTable)
3. Recompile your app.
4. WebSite Data Sources Window goes blank.

Fixing The Problem
1. Exclude the bad Reference from your project
2. Recompile
3. WebSite DataSources return
4. Return emergency ferret to cage.

Others On The Journey Of Pain
Fellow desperado gtrfinder also had to kill bad dlls to make his Website Data Sources Window populate. Others in that ASP.NET Forums thread report various workarounds. Good Luck!

What Is This Thing Called ReportViewer ?
Its like a baby brother to SQL Server Reporting Services Reports, but you don’t need SQL Server. The only dependency is the .NET 2.0 (or better) Framework. Nice for a free Reporting Solution for .NET. I got onto it while hunting for a way to easily do Drill-Down Reports in .NET. It ships with Visual Studio 2005 ans 2008. Here’s the MSDN write-up with some getting started tutorials.

OracleDataProvider: ORA-01861 Literal Does Not Match Format String

Got the above error calling a Stored Procedure via an OracleCommand.ExecuteNonQuery.

Not being an Oracle savant, I Googled the error and found it can come about when an Oracle TO_DATE function call cannot match the string literal with the supplied date picture (Excuse my atavistic lapse into COBOL jargon) in the Format string.

For example, TO_DATE(‘23/02/2009′, ‘DD-MON-YY’) produces ORA-01861 because the Format string specifies three character month and two character years, but received a two character month and a four character year.

In my case. however, the crash occurred on line one of the Stored Proc where a DATE parameter was listed in the contract viz:

CREATE OR REPLACE pCalculateStuff(
inDodgyDate IN Date,
inStudentNumber IN VARCHAR2,
inOrg_Number IN VARCHAR2)

and yet Oracle was still complaining with the above error.

Moreover, the parameters were explicitly named in the OracleParamter array I was using to populate the OracleCommand object which called the Stored Proc. I was expecting the parameters to resolve by name but they didn’t.

What I found was that the order of the parameters in the OracleParameter array must match the order of the parameters in the Stored Procedure, even though the OracleParameters are all explictly named in the .NET code

I had the following:

oraParams(0) = New OracleParameter("inStudentNumber", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(0).Value = studentNum.ToString()
oraParams(1) = New OracleParameter("inOrgTradeAutoNum", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(1).Value = orgTradeAutoNum.ToString()
oraParams(2) = New OracleParameter("inDateProcessing", OracleDbType.Date, ParameterDirection.Input)
oraParams(2).Value = termStartDate

oraCmd.CommandText = strStoredProcName
oraCmd.CommandType = CommandType.StoredProcedure

If (params.Length > 0) Then
For intParamCount = 0 To params.Length - 1
oraCmd.Parameters.Add(params(intParamCount))
Next
End If

' Code crashes here, giving ORA-01861
oraCmd.ExecuteNonQuery()

So, when I called ExecuteNonQuery on the StudentNumber was being bound to inDodgyDate, because it occupied parameter index 0 in the OracleParameters array so that ExecuteNonQuery threw ORA-01861 because ‘211946′ failed to parse into a Date

The fix was merely to re-order the position of the params in the OracleParameters array so that it matched the order in the Stored Procedure viz:

-- Make inDodgyDate the first parameter in the array
oraParams(0) = New OracleParameter("inDodgyDate", OracleDbType.Date, ParameterDirection.Input)
oraParams(0).Value = termStartDate

Lesson Learnt
The name of the OracleParameter is irrelevant when parameter binding. The order in which they are added to the OracleCommand is the only thing that matters.

Addendum 27-March-2009
Looks like I could have save myself a lot of trouble by setting

oraCmd.BindByName = True

Format BoundField During GridView OnRowDataBound

Got a new project on a VB.NET app. One page has a GridView using asp:BoundField controls with one of them bound to a Date field. The Date might contain ‘01/01/0001′ so I decided to display it as blank in that situtation.

Crunging The Confounded BoundField

I decided to handle the OnRowDataBound event which gives me access to the Row in GridViewRowEventArgs. But BoundField does not have a Control ID, so how to access the BoundField control from the Row…not possible to use Row.FindControl without a Control ID.

So what you do is access the Row’s TableCell collection giving the index of the GridView column you are interested in viz: e.Row.Cells[gridViewColumnIndex].Text

Here’s an example:

protected StudentGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
const DODGY_DATE_COLUMN = 3; — Date column is in 4th column of GridView
if (e.Row.RowType == DataRowControlType.DataRow)
{
— “Dodgy_Date” is the DataField property for the BoundField
Date dateVal = e.Row.DataItem(“Dodgy_Date”) as Date;
if (dateVal.Year == 1)
{
— Format ‘01/01/0001′ as blank
e.Row.Cells[DODGY_DATE_COLUMN].Text = string.Empty;
}
else
{
— Got a real date. Format it as short date.
e.Row.Cells[DODGY_DATE_COLUMN].Text = String.Format(“{0:d}”, dateVal)
}
}
}

Here’s a further example from MSDN