OleDbCommandBuilder and columns w/ spaces

[JuggaloBrotha]

My company has a sub for updating an access database table with a dataset. We pass it a SQL Select statement which then in the sub updates the DataAdapter using OleDb.OleDbCommandBuilder(TheDataAdapter)

this has been working fine for 2 years now, until now, one of the tables that needs to be updated in the database has 2 columns that have a space in the name. Getting the data is fine, we simply put brackets "[]" around the fields (columns) that have a space and it pulls all the data just fine, but the OleDb.OleDbCommandBuilder removes the bracket's when it generates the Delete and Insert statements which causes and error when the dataadapter's Update() method gets called.

Is there a way around this?

here's the class info: http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommandbuilder_members.aspx

 

[cjard]

Configure the data adapter yourself? Create an overload for this method that accepts a dataadapter already.

All the command builder does when you pass it a select statement is:

Deduce the table name
Get the schema for the table
Get the PK for the table

create statements of the form:

INSERT INTO {tablename}({columnnames}) VALUES({parameternames})
UPDATE {tablename} SET {columnname1} = {parametername1} .. WHERE {PK column1} = {originalparametername1} ...
DELETE FROM {tablename}({columnnames}) WHERE {PK column1} = {originalparametername1} ...


If you require assistance with the SQLs, let me know.

Alternately, there is nothing stopping you upgrading the code to accept a list of replacements and performing them on the UpdateCommand.CommandText etc... e.g. get the CommandBuilder to do most of the work for you, but then just tweak what it has written.

The other 2 options I would recommend as a better solution are:

Write a proper version of theis Data Access Layer, imitating .NET 2.0 (or get someone with .net 2 to create the code using the visual designer ,then rip it out and put it into your .net 1.1 project)

Do not use spaces in column names - it has always been a bad idea

 

[JuggaloBrotha]

i'll have a talk with my co-workers about this, thanx for the advice