OleDbDataAdapter: SELECT with field list?

[AdaHacker]

Forgive me if this is obvious, but I'm still getting used to ADO.NET and it is a Monday morning.

I'm making a simple form to display data from an Access database. The relevant code looks like this:

Dim conn As OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim conn_str As String
Dim default_cmd_text As String

conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source=C:\mydb.mdb"
conn = New OleDb.OleDbConnection(conn_str)

default_cmd_text = "select rate_code, basic_rate, supplies, usage from equipment_rates"
da = New OleDb.OleDbDataAdapter(default_cmd_text, conn)
ds = New DataSet()
da.Fill(ds, "rate_codes")

When I run the above code, it throws an OleDbException on the last line. However, if I change default_cmd_text to "select * from equipment_rates", then it works.

What am I missing here? The examples I've seen led me to believe that you could pass the Data Adapter an arbitrary SELECT query. Is that not the case?

 

[ThatRickGuy]

Are you sure those fields exist and are spelled correctly?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[gmmastros]

in default_cmd_txt, you reference rate_code, but the last line references rate_code[red]s[/red]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ThatRickGuy]

George, that last line the "rate_codes" parameter refers to the name to assign to the data table. But it does look like it could point at a possible type-o in the field list.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[AdaHacker]

Thanks for the replies!

I've figured it out. It wasn't a typo.

I did a little experimenting and it worked when I remove the "usage" field. A little Googling revealed that "usage" is a reserved word in Transact-SQL (which, I presume, is the dialect ADO.NET is using). Apparently it isn't in the dialect used by Access 97 (don't ask why we're still using Office 97), which is why it didn't show up when I tried the query in Access. Anyway, everything is fine if I bracket that field in the query, e.g.

select rate_code, basic_rate, supplies, [usage] from equipment_rates