Problem with datatable compute max(value) method

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have the following code that is getting (or should get) the max value
from an xml file. Even though the max value should be 21, it is always
returning 9:

DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.ReadXml(Server.MapPath"~/Home/XMLMenus/MenuCategory.xml"),XmlReadMode.InferSchema);

dt = ds.Tables[0];
string selectCommand = "Max(report_category_id)";
string filterCommand = "report_category_id > 0";
int nextNum =
Convert.ToInt32((object)dt.Compute(selectCommand,string.Empty));

DataRow dr = ds.Tables[0].NewRow();
dr["report_category_id"] = nextNum + 1;
dr["report_category"] = "Test";
ds.Tables[0].Rows.Add(dr);
DataGrid1.DataSource = dt;
DataGrid1.DataBind();

here is the xml:

<NewDataSet>
<Table>
<report_category_id>1</report_category_id>
<report_category>Accounting</report_category>
</Table>
<Table>
<report_category_id>5</report_category_id>
<report_category>Company</report_category>
</Table>
<Table>
<report_category_id>21</report_category_id>
<report_category>Continuous Improvement</report_category>
</Table>
<Table>
<report_category_id>6</report_category_id>
<report_category>Customer Care</report_category>
</Table>
<Table>
<report_category_id>7</report_category_id>
<report_category>Documentation</report_category>
</Table>
<Table>
<report_category_id>8</report_category_id>
<report_category>Employee</report_category>
</Table>
<Table>
<report_category_id>9</report_category_id>
<report_category>Engineering</report_category>
</Table>
<Table>
<report_category_id>10</report_category_id>
<report_category>Test</report_category>
</Table>

I have a feeling it is treating the xml values as strings because
removing the single digit numbers whos left char is equal to or less
than the left char of the max double digit returns the right value. In
other words 3 > 21 because 3 > 2 and 21 > 2. Any suggestions?
 
Hi Stephen,

You can check the column type by checking out column type:
Type type = ds.Tables[0].Columns["report_category_id"].DataType;

If the column type is not numeric, than you can use CONVERT function:
MAX(CONVERT(report_category_id, 'System.Int32'))
 
Thanks! That got me one step closer, however now I am getting the
error:

Syntax error in aggregate argument: Expecting a single column argument
with possible 'Child' qualifier.

The data type returned using your example was System.String
 
Stephen,

While off topic, I thought you might by interested in
http://queryadataset.com. The assembly I'm writing allows complex SELECT
queries against a DataSet.

I copied your XML fragment into one of the DataSets (look for Stephen on the
Upload DataSet page) avaiable and executed the following:

select Max(report_category_id) from
where report_category_id > 0

Sure enough, the result was 9. However, when I changed the query to:

select Max(cast(report_category_id as int)) from
where
report_category_id > 0

The correct result of 21 comes out.

Hope this helps
Ad.
 
Stephen,

One other thing, feel free to upload a more complete example of your DataSet
and try some other queries. If your data is sensitive, I can easily setup a
private page, just for you try.

Ad.
 
Ultimately I decided to use a schema in my xml document and type the
column as an integer.

Thanks for the help.
 
Back
Top