(Yet Another) OleDB Database Update Problem

First of all, let me thank you for at least trying to help. I have to finish a simple program by friday and although the code works I have a few problems:

I have a database with 2 tables "Table1" and "Table2"
Each Table have 4 fields which are the tablenumber + F + column number
So in table1 we have 1F1, 1F2, 1F3, 1F4
And in table 2 2F1, 2F2, 2F3, 2F4

The code uses an OleDbCommandBuilder to create the queries.

With the names I mentioned before the code works perfectly.

Adding spaces to the tablenames --> "Table 1" and "Table 2" I got errors. This was ofcourse rectified by using [ ] around the tablename.

When the fields also had spaces the DataAdapter failed to update the database.

I checked the Queries the CommandBuilder was returning and they didn't have [] around the fieldnames. As a workaround I change all the databases fields and tablenames to nonspaced names. This is unacceptable. I can't use the form data controls, It must all be done in code, because the code is for a class library.

I really need help on this and will listen to anybody and try anything. I have come up with writting code to change the queries by replacing the fieldnames with [fieldnames] but It will take a long time to program that, as I have to retrieve field names, replace them in the query e.t.c.

Help

Answer this question

(Yet Another) OleDB Database Update Problem

Jose Cruz
First of all it ia good practice not to use spaces in a names of the fields. There are always the ways to display different header text in a controls. Anyway, you can use SqlCommandBuilder or OledbCommandBuilder class to build your queries and both classes have QuotePrefix and QuoteSuffix properties. They define first and last characters for the names of the fields and tables. These two properties designed for the situations like yours. Assign [ to the QuotePrefix and ] to QuoteSuffix property during building of the commands and it should work
MattDoo
Thank you!

I had given up and was writting code to manually find the fields and replace them with "[" & fieldname & "]"

You just saved my life.

I owe you one,

Upsetian

(Lets just hope the concept works)
(Yet Another) OleDB Database Update Problem

Answer this question