Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Specific cast is not valid in linq query when compare two tables

Status
Not open for further replies.

ahmedsa2018

Programmer
Apr 25, 2018
67
EG
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

Code:
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:
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;
}
Image for debug attached with post
 
 https://files.engineering.com/getfile.aspx?folder=1197bbc4-480b-4f81-9c61-cbaafa2e206d&file=wrong_linq_query.jpg
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top