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

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.
 
S

Sylvain Lafontaine

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...
 
G

Guest

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...
 
S

Sylvain Lafontaine

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...
 
G

Guest

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
 
S

Sylvain Lafontaine

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
MS Access Query criteria Problem 2
Conditional query criteria in Access 2003 with SQL Server BE 4
The Many Flavors of SQL - Can a SQL Server query work in MS Access? 7
What's the Access equivalent for this SQL Server query? 12
Different results in SQL and Access 16
unable to delete invalid SQL Server records in Access or SQL Server 4

Top