ahmed elbarbary

ahmed elbarbary

  • 1.4k
  • 436
  • 17.1k

specific cast is not valid linq when compare two datatable

Apr 25 2018 1:37 PM

Problem
error display in linq query "specific cast is not valid"

at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)
at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName)

LinqQuery give error

  1. var query1 = (from x in table1.AsEnumerable()  
  2.                           join y in table2.AsEnumerable() on x.Field<int>("UnitCode") equals y.Field<int>("UnitCode")  
  3.                           where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")  
  4.                           select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = x.Field<decimal>("CurrentMeterReading") }).ToList(); 



When make debug the first data table tableReadingExcelsheet retrieve data from excel sheet .

second datatable readingfromInvoiceTablesql retrieve data from wahinvoice table in sql .

I need to get list of rows in excel sheet that have current reading less than

currentreading in wahinvoice table for same UnitCode then display in datagridview .


  1. private void button2_Click(object sender, EventArgs e)  
  2.         {  
  3.             DataTable tableReadingExcelsheet = new DataTable();  
  4.             tableReadingExcelsheet.Columns.AddRange(new DataColumn[] { new DataColumn("UnitCode"typeof(int)), new DataColumn("CurrentMeterReading"typeof(decimal)) });  
  5.             tableReadingExcelsheet = ShowdataFromExcel();  
  6.             DataTable readingfromInvoiceTablesql = new DataTable();  
  7.             readingfromInvoiceTablesql.Columns.AddRange(new DataColumn[] { new DataColumn("Serial"typeof(int)), new DataColumn("UnitCode"typeof(int)), new DataColumn("CurrentMeterReading"typeof(decimal)) });  
  8.             readingfromInvoiceTablesql = GetCurrentReadingUnitCodesql();  
  9.             var query1 = (from x in tableReadingExcelsheet.AsEnumerable()  
  10.                           join y in readingfromInvoiceTablesql.AsEnumerable() on x.Field<int>("UnitCode") equals y.Field<int>("UnitCode")  
  11.                           where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")  
  12.                           select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = x.Field<decimal>("CurrentMeterReading") }).ToList();  
  13.   
  14.             dataGridView1.DataSource = query1;  
  15.             dataGridView1.Refresh();  
  16.   
  17.         }  
  18. //get data from excel success  
  19. public System.Data.DataTable ShowdataFromExcel()  
  20.         {  
  21.             string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);  
  22.   
  23.             OleDbConnection con = new OleDbConnection(connectionString);  
  24.   
  25.   
  26.             con.Open();  
  27.   
  28.             string str = @"SELECT  [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM  [Sheet5$] ";  
  29.             OleDbCommand com = new OleDbCommand();  
  30.             com = new OleDbCommand(str, con);  
  31.             OleDbDataAdapter oledbda = new OleDbDataAdapter();  
  32.             oledbda = new OleDbDataAdapter(com);  
  33.             DataSet ds = new DataSet();  
  34.             ds = new DataSet();  
  35.             oledbda.Fill(ds, "[Sheet5$]");  
  36.             con.Close();  
  37.             System.Data.DataTable dt = new System.Data.DataTable();  
  38.             dt = ds.Tables["[Sheet5$]"];  
  39.             return dt;  
  40.   
  41.   
  42.         }  
  43. //get data from sql wahinvoice success  
  44. public System.Data.DataTable GetCurrentReadingUnitCodesql()  
  45.         {  
  46.             sqlquery = @"select Serial,UnitCode, CurrentMeterReading  
  47. from( select Serial,UnitCode, CurrentMeterReading, ROW_NUMBER() OVER(PARTITION BY UnitCode ORDER BY Serial desc) as rn  
  48. from WAHInvoice) as a  
  49. where rn = 1";  
  50.   
  51.   
  52.             System.Data.DataTable tbCurrentReading = DataAccess.ExecuteDataTable(sqlquery);  
  53.             return tbCurrentReading;  
  54.         } 

Image for debug


Answers (1)