OleDbDataCommand problem

I'm know the "old" implementation of ADO very well, and I
am now trying to get used to ADO.NET. I've always
preferred creating connection, commands and recordsets
programmaticaly since every application I've built that
needs data access allows for modifiable connection
settings. This leads me to my question:

When I create a OleDbConnection, OleDbCommand and
OleDbDataReader to retrieve data from an Access database,
everything works the way it should until I set the
CommandText for the OleDbCommand object. It seems that
if I try to order the results using the ORDER BY command
an OleDbException is thrown with the following
description:

Unspecified error: E_FAIL(0x80004005)

However, if I do not use and ORDER BY command, the
exception is not thrown. Here's the code:

public OleDbDataReader GetPopUps()
{
OleDbCommand cmd=new OleDbCommand();
OleDbDataReader dr;

cmd.Connection=mcn;
cmd.CommandType=CommandType.Text;
cmd.CommandText="SELECT PUKey, Domain FROM PopUp
ORDER BY Domain ASC";
dr = cmd.ExecuteReader();

cmd=null;
return dr;
}
Any help would be greatly appreciated.

Paul

William
Sun Aug 17 15:37:31 CDT 2003

Well, Domain definitely isn't a reserved word which can
cause this problem. The one thing that looks like it
could be a problem is the open method...are you sure that
you have an open connection? Datareaders need an 'open
and availabe' connection to work properly.

You might want to wrap the dr = cmd.executereader command
in a try catch block and use something like
Debug.WriteLine(myException.ToSTring) ... it will give
you some specific information on the nature of the
exception. When you say that you don't get the exception
when you don't use Order by, does that mean it works
fine? I know that seem slike a silly question, but I ask
only because it doesn't appear that the connection is
ever opened.

See what the exception information tells you, that'll
help us narrow it down.

Let me know.

Bill

W.G. Ryan
dotnetguru@comcast.nospam.net
www.knowdotnet.com
>-----Original Message-----
>I'm know the "old" implementation of ADO very well, and
I
>am now trying to get used to ADO.NET. I've always
>preferred creating connection, commands and recordsets
>programmaticaly since every application I've built that
>needs data access allows for modifiable connection
>settings. This leads me to my question:
>
>When I create a OleDbConnection, OleDbCommand and
>OleDbDataReader to retrieve data from an Access
database,
>everything works the way it should until I set the
>CommandText for the OleDbCommand object. It seems that
>if I try to order the results using the ORDER BY command
>an OleDbException is thrown with the following
>description:
>
> Unspecified error: E_FAIL(0x80004005)
>
>However, if I do not use and ORDER BY command, the
>exception is not thrown. Here's the code:
>
>public OleDbDataReader GetPopUps()
>{
> OleDbCommand cmd=new OleDbCommand();
> OleDbDataReader dr;
>
> cmd.Connection=mcn;
> cmd.CommandType=CommandType.Text;
> cmd.CommandText="SELECT PUKey, Domain FROM PopUp
>ORDER BY Domain ASC";
> dr = cmd.ExecuteReader();
>
> cmd=null;
> return dr;
>}
>Any help would be greatly appreciated.
>
>Paul
>.
>

Paul
Sun Aug 17 16:14:07 CDT 2003

As far as the connection state, I have another function
that opens the private connection (mcn) that was called
right before the call to GetPopUps().

Well, even though Domain doesn't appear to be a reserved
word, using brackets around the field name did the
trick. Hmmmm....Guess I'll make sure to use brackets
whenever referencing a field name, no matter what the
name is, to prevent this kind of thing from happening
again. This issue has to be with the OleDbCommand class
or a parent class, because the same query run in Access
works without a problem (without brackets).

Thanks for your help!

Paul

William
Sun Aug 17 17:12:28 CDT 2003

That's interesting. I've seen the problem before when a
reserved word is used and that's what I suspected at
first. However, when I looked at MS and a few other
places, there was no mention of it. Being a word that's
used pretty often in cs lingo, you almost expect that it
would be a reserved word and since the brackets fixed it,
I'm guessing it is and may just not be documented. A co-
worker of mine had a lot of trouble with an app he
converted b/c he used a lot of keywords in for field names
and it took a lot of rework to get it running. The OleDb
doesn't like reserved words, that's for sure.

Anyway, I'm glad you got it working.

Cheers,

Bill
>-----Original Message-----
>Well, Domain definitely isn't a reserved word which can
>cause this problem. The one thing that looks like it
>could be a problem is the open method...are you sure that
>you have an open connection? Datareaders need an 'open
>and availabe' connection to work properly.
>
>You might want to wrap the dr = cmd.executereader command
>in a try catch block and use something like
>Debug.WriteLine(myException.ToSTring) ... it will give
>you some specific information on the nature of the
>exception. When you say that you don't get the exception
>when you don't use Order by, does that mean it works
>fine? I know that seem slike a silly question, but I ask
>only because it doesn't appear that the connection is
>ever opened.
>
>See what the exception information tells you, that'll
>help us narrow it down.
>
>Let me know.
>
>Bill
>
>W.G. Ryan
>dotnetguru@comcast.nospam.net
>www.knowdotnet.com
>>-----Original Message-----
>>I'm know the "old" implementation of ADO very well, and
>I
>>am now trying to get used to ADO.NET. I've always
>>preferred creating connection, commands and recordsets
>>programmaticaly since every application I've built that
>>needs data access allows for modifiable connection
>>settings. This leads me to my question:
>>
>>When I create a OleDbConnection, OleDbCommand and
>>OleDbDataReader to retrieve data from an Access
>database,
>>everything works the way it should until I set the
>>CommandText for the OleDbCommand object. It seems that
>>if I try to order the results using the ORDER BY command
>>an OleDbException is thrown with the following
>>description:
>>
>> Unspecified error: E_FAIL(0x80004005)
>>
>>However, if I do not use and ORDER BY command, the
>>exception is not thrown. Here's the code:
>>
>>public OleDbDataReader GetPopUps()
>>{
>> OleDbCommand cmd=new OleDbCommand();
>> OleDbDataReader dr;
>>
>> cmd.Connection=mcn;
>> cmd.CommandType=CommandType.Text;
>> cmd.CommandText="SELECT PUKey, Domain FROM PopUp
>>ORDER BY Domain ASC";
>> dr = cmd.ExecuteReader();
>>
>> cmd=null;
>> return dr;
>>}
>>Any help would be greatly appreciated.
>>
>>Paul
>>.
>>
>.
>