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 - 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 . - 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;
-
}
0 1468 Sign in to post your reply or Sign up for a free account.
Similar topics
by: Paul |
last post by:
Hi I am trying to access a dropdown control in a table and am using the code
below but get the error specific cast is not valid.
Dim ctrl_dpn1 As DropDownList
ctrl_dpn1 =...
|
by: Topher |
last post by:
I have two tables the are generated by queries, call them A and B. When
something in the data is changed, the query generates a new Table B. I
need to use a query to compare table A and table B and...
|
by: szwejk |
last post by:
Hi!
How to get result od dataTable from Linq query? I have typied DataSet
and I want to join couple of tables. And I have a problem with change
this result to DataTable type. (I don't want to...
|
by: Satish |
last post by:
Hi everyone,
I need some help constructing a linq query on datatables.
I have 2 datatables as follows
RangeTable
MinValue MaxValue
0.00 0.050
0.051 0.10
0.101 0.15
0.151 0.20
|
by: Joey |
last post by:
I am querying a DataSet with LINQ. I am running into a problem when
trying to construct my query because in the "from" clause I do not
know the table name (range variable) until runtime. Possible...
|
by: shapper |
last post by:
Hello,
I have two Lists:
A = {ID, Name} = { (Null, John), (Null, Mary), (Null, Andrew), (Null,
Peter) }
B = {ID, Name} = { (1, John), (2, Robert), (3, Angela), (4, Andrew) }
I want to...
|
by: raylopez99 |
last post by:
Inspired by Chapter 8 of Albahari's excellent C#3.0 in a Nutshell
(this book is amazing, you must get it if you have to buy but one C#
book) as well as Appendix A of Jon Skeet's book, I am going...
|
by: alex21 |
last post by:
Ok i am trying to use a Linq query to access a dictionary.
public static Dictionary<string, Client> Clients = new Dictionary<string, Client>();Using this Linq query:
IEnumerable<Staff> loginquery...
|
by: scott1010 |
last post by:
Hello I am having problems with a Linq query. I need to return an ID field which is of type GUID (c.Id) along with other fields of type String.
I have tried both anonymous types and strongly typed...
|
by: sahithi3 |
last post by:
Hi All,
I need to perform server side validation using if condition
My view page consists of form page where the data entered in the fields must be validated if its present in the database or not...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |