G
Guest
Very strange problem: Executing my query against MS Access database using
OleDbProvider for Access, I am getting the value for first two columns '0'
instead of '1' in DataGrid. But if I connect to SQL Server using
OleDbProvider, it shows me the correct value i.e. 1 wherever it should be.
To debug i write the query to a file. Now copy the query from that file to
Acess SQL View and run. Here it shows the correct value 1 for the first two
columns wherever it should be 1.
I think this is the problem of OleDbData Provider for Access in .NET.
The first two columns are created on the fly i.e. these columns doesn’t
exist in table I just create as a view. But this query is populating the
correct value for the first two columns when I connect to SQL Server, but
connecting to MS Access using OleDb Provider to .NET it populates ‘0’ in
place of ‘1’ for the first two columns. Rest of the things are ok.
QUERY:
" SELECT "+
" (select Quantity from Items_Detail idt "+
" where idt.Barcode = MB.Barcode "+
" AND MB.inv_date='Friday, September 16, 2005'" +
" ) AS \"Qty on: Friday, September 16, 2005\"" +
" ,(select Quantity from Items_Detail idt "+
" where idt.Barcode = MB.Barcode "+
" AND MB.inv_date='Tuesday, September 20, 2005'"+
" ) AS \"Qty on: Tuesday, September 20, 2005\""+
" , idt.Barcode, idt.Place, idt.Description, idt.[Purchase Date],
idt.[Years Used], idt.[Condition], idt.[Actual], idt.[Elevative],
idt.[Depreciation], idt.[Notebook Value], idt.Present, idt.Absent,
idt.[Absence Reason] " +
" FROM Items_Detail idt, "+
" (" +
// /*The purpose of this query is to return the missing Barcodes in either
of two Dates*/
" SELECT Inv_Date, Barcode FROM Inventory inv "+
" WHERE "+
" inv.Barcode IN (select inventory.Barcode from inventory where
inv_date='Friday, September 16, 2005')"+
" AND inv.Barcode NOT IN (select inventory.Barcode from inventory where
inv_date='Tuesday, September 20, 2005')"+
" OR "+
" inv.Barcode IN (select inventory.Barcode from inventory where
inv_date='Tuesday, September 20, 2005')"+
" AND inv.Barcode NOT IN (select inventory.Barcode from inventory where
inv_date='Friday, September 16, 2005')"+
" ) MB " +
///*Missed Barcodes*/
" WHERE idt.Barcode = MB.Barcode "+
" AND (MB.Inv_Date = 'Friday, September 16, 2005' OR MB.Inv_Date = 'Tuesday,
September 20, 2005') ";
Arif.
OleDbProvider for Access, I am getting the value for first two columns '0'
instead of '1' in DataGrid. But if I connect to SQL Server using
OleDbProvider, it shows me the correct value i.e. 1 wherever it should be.
To debug i write the query to a file. Now copy the query from that file to
Acess SQL View and run. Here it shows the correct value 1 for the first two
columns wherever it should be 1.
I think this is the problem of OleDbData Provider for Access in .NET.
The first two columns are created on the fly i.e. these columns doesn’t
exist in table I just create as a view. But this query is populating the
correct value for the first two columns when I connect to SQL Server, but
connecting to MS Access using OleDb Provider to .NET it populates ‘0’ in
place of ‘1’ for the first two columns. Rest of the things are ok.
QUERY:
" SELECT "+
" (select Quantity from Items_Detail idt "+
" where idt.Barcode = MB.Barcode "+
" AND MB.inv_date='Friday, September 16, 2005'" +
" ) AS \"Qty on: Friday, September 16, 2005\"" +
" ,(select Quantity from Items_Detail idt "+
" where idt.Barcode = MB.Barcode "+
" AND MB.inv_date='Tuesday, September 20, 2005'"+
" ) AS \"Qty on: Tuesday, September 20, 2005\""+
" , idt.Barcode, idt.Place, idt.Description, idt.[Purchase Date],
idt.[Years Used], idt.[Condition], idt.[Actual], idt.[Elevative],
idt.[Depreciation], idt.[Notebook Value], idt.Present, idt.Absent,
idt.[Absence Reason] " +
" FROM Items_Detail idt, "+
" (" +
// /*The purpose of this query is to return the missing Barcodes in either
of two Dates*/
" SELECT Inv_Date, Barcode FROM Inventory inv "+
" WHERE "+
" inv.Barcode IN (select inventory.Barcode from inventory where
inv_date='Friday, September 16, 2005')"+
" AND inv.Barcode NOT IN (select inventory.Barcode from inventory where
inv_date='Tuesday, September 20, 2005')"+
" OR "+
" inv.Barcode IN (select inventory.Barcode from inventory where
inv_date='Tuesday, September 20, 2005')"+
" AND inv.Barcode NOT IN (select inventory.Barcode from inventory where
inv_date='Friday, September 16, 2005')"+
" ) MB " +
///*Missed Barcodes*/
" WHERE idt.Barcode = MB.Barcode "+
" AND (MB.Inv_Date = 'Friday, September 16, 2005' OR MB.Inv_Date = 'Tuesday,
September 20, 2005') ";
Arif.