Specific cast is not valid in linq query when compare two tables
Specific cast is not valid in linq query when compare two tables
(OP)
Specific cast is not valid in linq query when compare two tables
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
Details
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 .
Image for debug attached with post
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
CODE --> c#
var query1 = (from x in table1.AsEnumerable()
join y in table2.AsEnumerable() on x.Field<int>("UnitCode") equals y.Field<int>("UnitCode")
where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")
select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = x.Field<decimal>("CurrentMeterReading") }).ToList(); Details
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 .
CODE --> c#
private void button2_Click(object sender, EventArgs e)
{
DataTable tableReadingExcelsheet = new DataTable();
tableReadingExcelsheet.Columns.AddRange(new DataColumn[] { new DataColumn("UnitCode", typeof(int)), new DataColumn("CurrentMeterReading", typeof(decimal)) });
tableReadingExcelsheet = ShowdataFromExcel();
DataTable readingfromInvoiceTablesql = new DataTable();
readingfromInvoiceTablesql.Columns.AddRange(new DataColumn[] { new DataColumn("Serial", typeof(int)), new DataColumn("UnitCode", typeof(int)), new DataColumn("CurrentMeterReading", typeof(decimal)) });
readingfromInvoiceTablesql = GetCurrentReadingUnitCodesql();
var query1 = (from x in tableReadingExcelsheet.AsEnumerable()
join y in readingfromInvoiceTablesql.AsEnumerable() on x.Field<int>("UnitCode") equals y.Field<int>("UnitCode")
where y.Field<decimal>("CurrentMeterReading") > x.Field<decimal>("CurrentMeterReading")
select new { UnitCode = x.Field<int>("UnitCode"), CurrentReading = x.Field<decimal>("CurrentMeterReading") }).ToList();
dataGridView1.DataSource = query1;
dataGridView1.Refresh();
}
//get data from excel success
public System.Data.DataTable ShowdataFromExcel()
{
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);
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string str = @"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$] ";
OleDbCommand com = new OleDbCommand();
com = new OleDbCommand(str, con);
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "[Sheet5$]");
con.Close();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["[Sheet5$]"];
return dt;
}
//get data from sql wahinvoice success
public System.Data.DataTable GetCurrentReadingUnitCodesql()
{
sqlquery = @"select Serial,UnitCode, CurrentMeterReading
from( select Serial,UnitCode, CurrentMeterReading, ROW_NUMBER() OVER(PARTITION BY UnitCode ORDER BY Serial desc) as rn
from WAHInvoice) as a
where rn = 1";
System.Data.DataTable tbCurrentReading = DataAccess.ExecuteDataTable(sqlquery);
return tbCurrentReading;
} 
Talk To Other Members