ASP.NET DataTable.Select method doesn't return rows.

You've got a small bug that only occurs on one particular server, that server is owned by someone else so you can't remote debug. You can't debug locally on your development machine, because there the bug is absent, everything works. So what do you do to diagnose the problem? Use the event log and or file system and print out your own debugging info.

So anyway the background. We have a large bespoke ASP.NET web application that manages recruitment for a large corporation. It's hosted on a private server that we have limited access to. A part of this distributed application is responsible for correspondence; email and letter merging for mass mailings. Within this component a couple of related DataTable objects are passed in along with an MS Word document template, and the data is merged. The first table contains a list of recipients, the other contains some repeating details (a list of required documents, or a list of booked courses for example) So it's a one to many relationship. The primary table is iterated one row at a time and pulls the related rows from the foreign key DataTable using the Select method of the that DataTable object. This returns an array of matching DataRow objects which are iterated into the template.

string filter = key + “ = “ + keyVal;

DataRow[] rows = detail.Select(filter);

foreach(DataRow row in rows)

{

//merge the data into the template.

}

The string filter argument for the Select method is a simple concatenation of the foreign key column name and the value to be selected. The key column is an auto identity integer column. The code was tested and worked perfectly on the development PC. However, on the staging server no rows were being returned even though we knew they existed. We couldn't debug on this server so we wrote our own debugging statements to the servers Application event log. So why would the filter return rows on the development computer but not another? We decided to serialize the table including schema information to an xml file onto the servers file system to take a closer look

detail.TableName = “detail”; //Without this the WriteXml method throws an exception.

detail.WriteXml(@“C:\Temp\detail.xml”, XmlWriteMode.WriteSchema, false);

And there was the answer.

<xs:element name="ApplicantId" type="xs:string" minOccurs="0" />

On the development machine the foreign key column was being correctly recognised as being of integer data type:

<xs:element name="ApplicantId" type="xs:int" minOccurs="0" />

But, on the staging server it was showing in the schema as a string field!

The fix was to change the filter to use single quote marks around the foreign key value.

string filter = key + “ = '“ + keyVal + “'”;

As this works in both scenarios. So the rule of thumb would seem to be: To use single quotes around your criteria parameter. I often see single quotes in SQL statements for integer columns and it has always irked me (being a bit of a purist – typical of my ilk) But after today I have a new respect for it .I haven't figured out the exact reason for this behaviour as the .NET framework is exactly the same version on both machines. My gut instinct, and therefore prime suspect is: MDAC.