9 Comments
  • Posted in:
  • C#

Today I had a DataTable object with the top x rows of a query.

select top 23 * from products

So my Asp.Net C# code looked like this:

private void SampleMethod(int toShow)
{
    string sql = "SELECT top " + toShow + " * from products";
    SqlCommand com = new SqlCommand(sql);
    DataTable dt = dal.GetDataTable(com);
    if (dt.Rows.Count > 0)
    {
        var a = dt.AsEnumerable().Where(
                p => p.Field<int>("stock") > p.Field<int>("ProductMinStock"))
        DataList2.DataSource = a;
        DataList2.DataBind();
    }
}

This crashed as you might have noticed. The error was:

[more]

DataBinding: System.Data.DataRow does not contain a property with the name 


DataRow does not contain

The problem was, that I did not specify the columns. The unfiltered DataTable could be bind to the DataList. Those DataRows where known. Since I did not use Entity Framework, but just try to filter a DataTable object which holds a DataRowCollection, I had to specify the return object(s). The types have to respond with your DataColumns in your database. You can set everything to string and see where it crashes if you do not know what type to use and change it. That’s how I found out that a field which should have been an int(32) was a float in the database (and therefor should have used double in C#)


Here is my final code, which has exactly the same columns as the unfiltered DataTable so that I can bind both result sets to the same DataList.

var a = dt.AsEnumerable().Where(
    p => p.Field<int>("stock") > p.Field<int>("ProductMinStock")).Select(p => new {
        ProductPictureName = p.Field<string>("ProductPictureName"),
        ProductCode = p.Field<string>("ProductCode"),
        ProductDescription = p.Field<string>("ProductDescription"),
        price = p.Field<double>("price"),
        productid = p.Field<Int32>("productid"),
        productpricebreak = p.Field<double>("productpricebreak")
    });

I hope that someone might have found this snippet useful to see how to manually specify the 'unknown' fields.

Good luck!

kick it on DotNetKicks.com Shout it
Pin on pinterest Plus on Googleplus Post on LinkedIn

Comments

Comment by DotNetKicks.com

Filter a DataTable with LINQ to Objects

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Comment by DotNetShoutout

Filter a DataTable with LINQ to Objects

Thank you for submitting this cool story - Trackback from DotNetShoutout

Comment by syngu.com

Pingback from syngu.com

Filter a DataTable with LINQ to Objects | .NET, LINQ | Syngu

Comment by JP Hellemons

This is also a nice resource if you need to filter a DataTable and need to return a DataTable. Filtering with LINQ is the easiest way to apply filtering.

msdn.microsoft.com/en-us/library/bb386921.aspx

JP Hellemons
Comment by Nevin Tom

Getting an error Specified cast not valid

p => p.Field<int>("user_id") > p.Field<int>("user_type")).Select(p => new
{

Nevin Tom
Comment by JP Hellemons

Hi Nevin Tom, are user_id and user_type really int? no bigint (int64)

JP Hellemons
Comment by nevin tom

Hi JP Hellemons
thanks for your replay...
they are of int type..(int32)

nevin tom
Comment by JP Hellemons

and cannot be null?

JP Hellemons
Comment by JP Hellemons

@nevin tom, what is below `new {` ? maybe there is the casting error. please post to stackoverflow or here in the comments.

JP Hellemons