Same Query + OK in SQL Server + Error in MS Access: E_FAIL........

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my C# application all queries runs well both with MS Access and SQL Server
databses. But only the following query runs well on SLQ Server2000 but with
MS Access gives error
=> "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

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') ";



The connection strings I am using are as follows:

Connection Srting for SLQ Server:
cn_str = "Provider=SQLOLEDB.1;Server=arif_nb\\cArabic; Database=BassamDb;
User ID=sa; Pwd=; Integrated Security=false;";

Connection Srting for MS Access:
cn_str = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" +
Settings.database + ";jet oledb:Database Password=" + Settings.pwd;


This is somehow a complex query and I don't know that MS Access supports
this type of queries. In SQL Server it is working well.

Please help in identiying that why this quey gives Error =>
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." with MS Access
database, while works well with SQL Server.

Arif.
 
Enclose dates between # instead of ' , remove all comments /*...*/ and
enclose names of alias between [] instead of \" . For dates, I don't know
if Access will recognise #Friday, September 16, 2005# .

Instead of giving us this big query, you should have took the time of making
some tests with simple queries to learn some of the differences T-SQL and
Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Arif said:
In my C# application all queries runs well both with MS Access and SQL
Server
databses. But only the following query runs well on SLQ Server2000 but
with
MS Access gives error
=> "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

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') ";



The connection strings I am using are as follows:

Connection Srting for SLQ Server:
cn_str = "Provider=SQLOLEDB.1;Server=arif_nb\\cArabic; Database=BassamDb;
User ID=sa; Pwd=; Integrated Security=false;";

Connection Srting for MS Access:
cn_str = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" +
Settings.database + ";jet oledb:Database Password=" + Settings.pwd;


This is somehow a complex query and I don't know that MS Access supports
this type of queries. In SQL Server it is working well.

Please help in identiying that why this quey gives Error =>
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." with MS Access
database, while works well with SQL Server.

Arif.
Click to expand...
 
Thanks Sylvain,

removing comments /*...*/ now it is working fine also with MS Access(with
comments it was working well with SQL Server).

But another very strange problem: I am getting the value for first two
columns '0' instead of '1' in DataGrid(as I am using OleDbProvider for
Access in my C# application. 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. Can
you pleaes figure out the problem.

Arif.

Sylvain Lafontaine said:
Enclose dates between # instead of ' , remove all comments /*...*/ and
enclose names of alias between [] instead of \" . For dates, I don't know
if Access will recognise #Friday, September 16, 2005# .

Instead of giving us this big query, you should have took the time of making
some tests with simple queries to learn some of the differences T-SQL and
Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Arif said:
In my C# application all queries runs well both with MS Access and SQL
Server
databses. But only the following query runs well on SLQ Server2000 but
with
MS Access gives error
=> "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

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') ";



The connection strings I am using are as follows:

Connection Srting for SLQ Server:
cn_str = "Provider=SQLOLEDB.1;Server=arif_nb\\cArabic; Database=BassamDb;
User ID=sa; Pwd=; Integrated Security=false;";

Connection Srting for MS Access:
cn_str = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" +
Settings.database + ";jet oledb:Database Password=" + Settings.pwd;


This is somehow a complex query and I don't know that MS Access supports
this type of queries. In SQL Server it is working well.

Please help in identiying that why this quey gives Error =>
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." with MS Access
database, while works well with SQL Server.

Arif.
Click to expand...
Click to expand...
 
Sorry, I cannot figure out your problem.

However, you should make sure that you don't have a regionalisation problem
with dates and that what you have pasted into MS-Access is really the same
thing as what's you have sent to the JET driver with the OleDbData provider.

It's possible that there is a bug with this provider, however the most
likely explanation if that there is a bug somewhere in your code or in the
sql statement.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Arif said:
Thanks Sylvain,

removing comments /*...*/ now it is working fine also with MS Access(with
comments it was working well with SQL Server).

But another very strange problem: I am getting the value for first two
columns '0' instead of '1' in DataGrid(as I am using OleDbProvider for
Access in my C# application. 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. Can
you pleaes figure out the problem.

Arif.

Sylvain Lafontaine said:
Enclose dates between # instead of ' , remove all comments /*...*/ and
enclose names of alias between [] instead of \" . For dates, I don't
know
if Access will recognise #Friday, September 16, 2005# .

Instead of giving us this big query, you should have took the time of
making
some tests with simple queries to learn some of the differences T-SQL and
Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Arif said:
In my C# application all queries runs well both with MS Access and SQL
Server
databses. But only the following query runs well on SLQ Server2000 but
with
MS Access gives error
=> "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

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') ";



The connection strings I am using are as follows:

Connection Srting for SLQ Server:
cn_str = "Provider=SQLOLEDB.1;Server=arif_nb\\cArabic;
Database=BassamDb;
User ID=sa; Pwd=; Integrated Security=false;";

Connection Srting for MS Access:
cn_str = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" +
Settings.database + ";jet oledb:Database Password=" + Settings.pwd;


This is somehow a complex query and I don't know that MS Access
supports
this type of queries. In SQL Server it is working well.

Please help in identiying that why this quey gives Error =>
"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." with MS
Access
database, while works well with SQL Server.

Arif.
Click to expand...
Click to expand...
Click to expand...
 
Hi...

Your solution is good.
But even though "Language" is not reserved word in Any of the MS Access
version then also it gives me the erro. When I have used [Language] instead
of Language, the error is solved.

Why the access gives error for Language word?

Thanks in advance

Raj
 
Language is not documented as a reserved word but it is possible that it is
for the SQL parser; I don't know.

If [Language] works but Language don't, then why asking?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." ...... 0
OleDbData Provider for Access + DataGrid shows '0' instead of '1' 2
The Many Flavors of SQL - Can a SQL Server query work in MS Access? 7
Conditional query criteria in Access 2003 with SQL Server BE 4
Coverting SQL from MS Access to SQL 2
Query does not display fields in same order as SQL or Design View 1
Convert SQL Server Procedure To Access Query 20
VS 2005 MS SQL server express query problem 3

Back
Top