Access field names and brackets problem [Resolved!]

[salvelinus]

I have an Access db (actually, about 60 of them) that has a table with
a field named Q#. This table gets read into a dataset. In the dataset,
the field is also called Q#. But when I try to update the db, an error
occurs because Access wants the field to be surrounded by brackets -
[Q#] - when written back. I know this is the problem, because if I
change the fieldname to QNum, it works fine.
Using the commandbuilder, or building the INSERT, UPDATE, or DELETE
manually without brackets, the error is "Syntax error in INSERT
statement..." (or UPDATE, etc). When I build them manually with
brackets, the error is "No value given for one or more required
parameters..."
An example of the manual statement is: INSERT INTO QS (Q#, QText)
VALUES(?, ?)
I don't want to change the fieldname in Access because there are so
many db's, and the field is used in many queries, forms, etc. I want
to make all changes to the dataset and update all at once. It works
using cmd.ExecuteNonQuery, but that's not what I want. Is there a way
to get this to work? Thanks.

[Mike Hildner]

My two cents:

Stick with standard names in the DB - if someone created a field named Q#, they probably don't know SQL all that well - best if well designed from the get go.

But seeing as that's what you have (I've inherited DBs like that also), you could manually wrap all the columns names in square brackets - it won't hurt anything (except your app's performance, I guess).

Mike

[salvelinus]

I know. The db's were designed several years ago by someone with no experience in Access (she also used lots of macros, which are a pain to go through and debug). I've already told everyone else (that's two other people) not to use names like that, or more than one word for table names. Eventually we'll be converting to mySql, so I can design that better, only one or two db's as well.

Anyway, would surrounding all field names with brackets make a difference? Surrounding the problem names with brackets doesn't fix it.

[Mike Hildner]

Anyway, would surrounding all field names with brackets make a difference? Surrounding the problem names with brackets doesn't fix it.

Oh, sorry, I guess I read your original post as the brackets did fix it. I don't use Access. Does it not like square brackets? Would quotes work?

[salvelinus]

Access is weird. It allows Q# as a field name, but because of the #, code has to surround the field name with brackets to run queries on the db. Same if you use two words; SomeField is ok, but Some Field has to be [Some Field] in code. Reading it out of the db as Q# is ok.
I think the problem is that [Q#] doesn't exist in the dataset; Q# does. So adding brackets manually throws the dataset off; not adding them throws Access off. This would explain why using ExecuteNonQuery with brackets works - no dataset involved.
This is the basic problem, unless I'm on the wrong track.

[Mike Hildner]

That's making sense now. How are you populating your dataset? Can you use an AS statement? e.g. SELECT Q# AS QNum FROM MyTable.

I use MS SQL 2000, so not too sure about Access.

[salvelinus]

Interesting idea. I'l try it out and let you know.

[salvelinus]

Unfortunately, couldn't get that to work either, probably because the dataset has QNum while the table has Q#. The datatable in the dataset has the actual field name as QNum.
I'd have to do some kind of AS statement when writing back to the db, which I don't think is possible.
For the time I've spent on this (parts of five days), I probably could've converted the names in all the db's. But dammit, this ought to be a simple procedure.

[Mike Hildner]

I have no idea. Not sure if it will help, but using sql server this works fine:

Dim conn As SqlConnection = New SqlConnection
        conn.ConnectionString = "Persist Security Info=False;" & _
                        "Integrated Security=SSPI;" & _
                        "database=Dispatch;" & _
                        "server=127.0.0.1"
 
        Dim ds As New DataSet("MyData")
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand("select q# from mytable", conn)
        da.Fill(ds)
        da.UpdateCommand = New SqlCommand("update mytable set q# = 'updated from vb.net'", conn)
        Dim dr As DataRow = ds.Tables(0).Rows(0)
        dr("q#") = "dummy string"
        da.Update(ds)

[salvelinus]

Yeah, I think it's an Access issue. Thanks for trying. I thought about using datarows, but who knows how many changes the user will make? I suppose it could be done, but it ought to be simple. It's all MS, after all, not like trying to get it to work with AS/400.

[salvelinus]

Got it! Have to set the QuotePrefix and QuoteSuffix properties of the commandbuilder to [ and ]. Man, that's a relief. Haven't seen those mentioned in any forum, google group or book anywhere.