Updating Database where Field Column Name has a space in it

[richy_latham]

Hi

I'm hoping this has a nice easy solution. I have a small C# app that
adds some rows to an Access database. Only problem is one of the
columns was originally named "Parent Alias".

When I went to update it I get the following error message "An
unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll"

I do Quick Watch on the dataset and when I drill down to
tables>List>Rows>RowError I can see this text "Syntax error (missing
operator) in query expression '@Parent Alias'."

This is the current insert command SQL query text "INSERT INTO
Paths(Alias,[Parent Alias],Engineer) VALUES (@Alias,@Parent
Alias,@Engineer)",connection);"

And this is the text for the parameter

....new OleDbParameter("@Parent Alias",OleDbType.VarChar,19,"[Parent
Alias]");

I've tried all sorts of combinations of square brackets both in the SQL
and in the parameter for @Parent Alias.

If I change Parent Alias to ParentAlias in the database and code it
works fine so it has to be the space in the column header.

Anyone know a way of working around this at all please?

Thanks

Rich

 

[Paul Clement]

On 24 Jan 2006 11:01:08 -0800, (e-mail address removed) wrote:

¤ Hi
¤
¤ I'm hoping this has a nice easy solution. I have a small C# app that
¤ adds some rows to an Access database. Only problem is one of the
¤ columns was originally named "Parent Alias".
¤
¤ When I went to update it I get the following error message "An
¤ unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
¤ in system.data.dll"
¤
¤ I do Quick Watch on the dataset and when I drill down to
¤ tables>List>Rows>RowError I can see this text "Syntax error (missing
¤ operator) in query expression '@Parent Alias'."
¤
¤ This is the current insert command SQL query text "INSERT INTO
¤ Paths(Alias,[Parent Alias],Engineer) VALUES (@Alias,@Parent
¤ Alias,@engineer)",connection);"
¤
¤ And this is the text for the parameter
¤
¤ ...new OleDbParameter("@Parent Alias",OleDbType.VarChar,19,"[Parent
¤ Alias]");
¤
¤ I've tried all sorts of combinations of square brackets both in the SQL
¤ and in the parameter for @Parent Alias.
¤
¤ If I change Parent Alias to ParentAlias in the database and code it
¤ works fine so it has to be the space in the column header.
¤
¤ Anyone know a way of working around this at all please?

OLEDB doesn't really support named parameters so you should probably use question marks instead.
Whether you specify named parameters or not, they are handled based upon their ordinal and not their
name.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[richy_latham]

Thanks for the reply. I found an answer (which I'll include it in case
anyone else has the same bother.) I have found that with Access I can
use named parameters and just question marks. Both work but not sure if
on a bigger system one method has performance difference over the
other.

Anyway....the answer.

Use the brackets only in the SQL statement e.g

OleDbCommand insertCommand=new OleDbCommand("INSERT INTO
Paths(Alias,[Parent Alias],Engineer) VALUES
(@Alias,@ParentAlias,@Engineer)",connection);

For the parameter I normally put the column name with the @ at the
start so in this case took the space out as its not really looking at
the column name.

When refering to the column (i.e. when making a new row) then use the
column name as is e.g

newRow["Parent Alias"]=template.Name;

And all should work OK.

 

[Paul Clement]

On 27 Jan 2006 07:31:59 -0800, (e-mail address removed) wrote:

¤ Thanks for the reply. I found an answer (which I'll include it in case
¤ anyone else has the same bother.) I have found that with Access I can
¤ use named parameters and just question marks. Both work but not sure if
¤ on a bigger system one method has performance difference over the
¤ other.
¤
¤ Anyway....the answer.
¤
¤ Use the brackets only in the SQL statement e.g
¤
¤ OleDbCommand insertCommand=new OleDbCommand("INSERT INTO
¤ Paths(Alias,[Parent Alias],Engineer) VALUES
¤ (@Alias,@ParentAlias,@engineer)",connection);
¤
¤ For the parameter I normally put the column name with the @ at the
¤ start so in this case took the space out as its not really looking at
¤ the column name.
¤
¤ When refering to the column (i.e. when making a new row) then use the
¤ column name as is e.g
¤
¤ newRow["Parent Alias"]=template.Name;
¤
¤ And all should work OK.

Yes, you can use named parameters but they still must appear in the order that they are defined in
the Parameter collection.


Paul
~~~~
Microsoft MVP (Visual Basic)