Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I am trying to select the Record where branchid=1 From DataTable using Linq C#

My Code:
C#
DataSet ds = new DataSet();
ds = obj.Bind(); //from database
 DataSet ds1 = new DataSet();
 DataTable dt = ds.Tables[0];
 IEnumerable<DataRow> query = from element in dt.AsEnumerable()

                                   select element;

  IEnumerable<DataRow> query1 = query.Where(p => p.Field<int>("branchid") == 1);//error
  DataTable dt1 = query1.CopyToDataTable<DataRow>();
  ds1.Tables.Add(dt1);


From this Im getting this Error
C#
": System.InvalidCastException: Specified cast is not valid."
Posted
Updated 22-Apr-17 23:14pm
v5
Comments
DamithSL 22-May-14 9:11am    
what is your database, what is your column type?
King Fisher 22-May-14 13:02pm    
column Datatype is bigint .
Matt T Heffron 23-May-14 12:52pm    
T-SQL bigint corresponds to c# type long (Int64)
NOT int (Int32)
King Fisher 6-Jun-14 7:55am    
can you tell me If float?
Matt T Heffron 6-Jun-14 11:59am    
I don't understand your question here.
http://msdn.microsoft.com/en-us/library/ms187752.aspx documents all of the T-SQL data types.
C# "float" is "real" (or "float(24)") in T-SQL

"branchid" is either
- not an int
- null.
 
Share this answer
 
Comments
King Fisher 16-May-14 8:22am    
Thanks for Reply ,
Branchid datatype is Int in my Database and none of the Integer Datatype column is Working sir,Its Throwing the Same Error.
but if i use any Varchar column then its Working.
:(
Bernhard Hiller 16-May-14 9:18am    
Then: because the value is NULL.
King Fisher 16-May-14 9:21am    
there is no null value at the Column sir .its says the Same error when i use datetime datatype Column also.
Matt T Heffron 16-May-14 16:45pm    
Is the branchid column NULL-able?
Have you tried p.Field<int?>("branchid")
 
In any case, the linq statement that assigns to query is pointless, just use dt.AsEnumerable() where you have query.
King Fisher 17-May-14 0:18am    
have a look on my code sir?
Did i missed anything?
add below line and check what is the type you get as data type
C#
var type = dt.Columns["branchid"].DataType;

you need to cast the column to above type. You say it as int in the database but in your dataset it could be one of below
C#
Int16
Int32
Int64
SByte
Single
UInt16
UInt32
UInt64

Better you debug and confirm the data type.

as per your below comment
Quote:
column Datatype is bigint .

try with Int64
C#
IEnumerable<DataRow> query1 = query.Where(p => p.Field<Int64>("branchid") == 1);
 
Share this answer
 
v2
Comments
Ajith K Gatty 23-May-14 1:51am    
Nice explaination...
King Fisher 26-May-14 0:20am    
thanks its working .i used Int64 .thanks a lot. :)
DamithSL 26-May-14 0:58am    
you are welcome!
Snesh Prajapati 29-May-14 23:28pm    
Absolute answer..thanks
Try this:
XML
IEnumerable<DataRow> query = from element in dt.AsEnumerable()
                             where(element.Field<int>("branchid") == 1)
                             select new
                             {
                                 Field1 = element.Field<int>("branchid"),
                                 Field2 = element.Field<type>("otherfield")
                             };

Remeber: C# is case sensitive!
branchid is not the same as BranchId.

And... Please, read my comment to the question ;)
 
Share this answer
 
v2
Comments
King Fisher 26-May-14 0:21am    
thank you.solution 2 is working. ;)
IEnumerable<datarow> query1 = query.Where(p => p.Field("branchid").ToString == "1")
C#

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900