×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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;
} 
Image for debug attached with post

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close