insert record into field names that contain brackets

Hi there,
I am having difficuly inserting a record into the database. I came to
realize that it may be because of my column name that contains brackets. It
inserts just fine with other fields that are the same data type but it stops
at the bracketed field name. How can I get around this? I can change the
column name to exclude any spaces because it's a pre-defined format. I tried
bracketing the field name within my code so it's the same as in the design
view but it still doesn't work. Can someone help me please. Here's my code
below:
----
--
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will
not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
Thanks!luv2travel wrote:
> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains
> brackets. It inserts just fine with other fields that are the same
> data type but it stops at the bracketed field name. How can I get
> around this? I can change the column name to exclude any spaces
> because it's a pre-defined format. I tried bracketing the field name
> within my code so it's the same as in the design view but it still
> doesn't work. Can someone help me please. Here's my code below:
> ----
--
Can you post your table DDL.
In the meantime, try this:
create table ugly_columns (
col_normal int not null,
"col_ug]y" int not null )
insert into ugly_columns (
col_normal,
"col_ug]y" )
values (
1,
2 )
Select * from ugly_columns
David Gugick
Imceda Software
www.imceda.com|||This is what I imagine the DDL would look like behind the scenes but I'm not
sure how SQL Server handles attribute names that contain spaces. All I know
is that when it appears in the design view, it's contained within square
brackets.
CREATE TABLE EMPLOYEES (
NAME varchar(20),
ADDRESS varchar(50),
EMAIL varchar(50),
[NUM CARS] int
)
I tried your suggestion using insert and it worked just fine with the
brackets. The only problem with using Insert is that I have several
attributes I would need to concatenate with their corresponding values. The
method I used makes it easier for me to query but somehow it doesn't like th
e
brackets. Any ideas why? Thanks.
"David Gugick" wrote:

> luv2travel wrote:
> Can you post your table DDL.
> In the meantime, try this:
> create table ugly_columns (
> col_normal int not null,
> "col_ug]y" int not null )
> insert into ugly_columns (
> col_normal,
> "col_ug]y" )
> values (
> 1,
> 2 )
> Select * from ugly_columns
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||luv2travel wrote:
> This is what I imagine the DDL would look like behind the scenes but
> I'm not sure how SQL Server handles attribute names that contain
> spaces. All I know is that when it appears in the design view, it's
> contained within square brackets.
> CREATE TABLE EMPLOYEES (
> NAME varchar(20),
> ADDRESS varchar(50),
> EMAIL varchar(50),
> [NUM CARS] int
> )
Brackets are for object names like tables and procedures unless you have
quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
Exec [dbo].[My Proc Has a Poor Name]
or
Exec "dbo"."My Proc Has a Poor Name"
Select * from [Hyphens-In-This-Table]
or
Select * from "Hyphens-In-This-Table"
From BOL:
The body of the identifier can contain any combination of characters in
the current code page except the delimiting characters themselves.
Why is there a bracket in the column name in the first place?
David Gugick
Imceda Software
www.imceda.com|||If you open Enterprise Manager and enter a column name that contains a space
in Design View, SQL Server will automatically embrace it with brackets.
There's no way getting around it.
"David Gugick" wrote:

> luv2travel wrote:
>
> Brackets are for object names like tables and procedures unless you have
> quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
> Exec [dbo].[My Proc Has a Poor Name]
> or
> Exec "dbo"."My Proc Has a Poor Name"
> Select * from [Hyphens-In-This-Table]
> or
> Select * from "Hyphens-In-This-Table"
> From BOL:
> The body of the identifier can contain any combination of characters in
> the current code page except the delimiting characters themselves.
> Why is there a bracket in the column name in the first place?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||luv2travel wrote:
> If you open Enterprise Manager and enter a column name that contains
> a space in Design View, SQL Server will automatically embrace it with
> brackets. There's no way getting around it.
>
But in that case the brackets are not in the column, they are just the
delimiters used in the UI to prevent SQL Server throwing an error.
If you run sp_help <table>, you won't see brackets:
For example:
create table ugly_columns (
col_normal int not null,
[col space] int not null )
sp_help ugly_columns
Column_name
--
col_normal
col space
When you access the column from your code, you need to delimite the
column using brackets or double quotes if quoted identifiers are on.
I don't understand your code well enough to know why you are having a
problem. What is the error you are receiving?
David Gugick
Imceda Software
www.imceda.com|||Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
Mink', '4') it worked just fine but if I leave out the brackets for Num Cars
,
it throws a run-time error. So I tried applying the same thing to the code
below by preserving the brackets and it threw an unhandled exception. Why
does it work one way and not the other? Is something wrong with the code
below?
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will
not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
"luv2travel" wrote:

> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains brackets. I
t
> inserts just fine with other fields that are the same data type but it sto
ps
> at the bracketed field name. How can I get around this? I can change the
> column name to exclude any spaces because it's a pre-defined format. I tri
ed
> bracketing the field name within my code so it's the same as in the design
> view but it still doesn't work. Can someone help me please. Here's my code
> below:
> ----
--
> COleSafeArray vaFieldList;
> vaFieldList.CreateOneDim(VT_VARIANT, 4);
> COleSafeArray vaValueArray;
> vaValueArray.CreateOneDim(VT_VARIANT, 4);
> long lArrayIndex[1];
> lArrayIndex[0] = 0;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
> lArrayIndex[0] = 1;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
> lArrayIndex[0] = 2;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
> lArrayIndex[0] = 3;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //wi
ll not
> insert this field
> lArrayIndex[0] = 0;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
> lArrayIndex[0] = 1;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
> lArrayIndex[0] = 2;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
> lArrayIndex[0] = 3;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
> pRecordset->AddNew(vaFieldList, vaValueArray);
> pRecordset->Close();
> Thanks!
>|||On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:

>Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cind
y
>Mink', '4') it worked just fine but if I leave out the brackets for Num Car
s,
>it throws a run-time error. So I tried applying the same thing to the code
>below by preserving the brackets and it threw an unhandled exception. Why
>does it work one way and not the other? Is something wrong with the code
>below?
(snip)
Hi luv2travel,
I don't recognise the language you are using. But it's clear that you
are not feeding it an ready-to-chew query. You are providing column
names and data and the programming language or calling interface somehow
transforms this into a query to feed to SQL Server.
Two things you might try, both based on the idea that maybe this
language "knows" that columns names with spaces must be bracketed or
quoted and therefore adds extra brackets or quotes to those you already
supplied:
1. Set up a profiler trace to capture the query actually sent to SQL
Server. This will provide absolute certainty about what's going on
behind the scenes.
2. Change this line:
>vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //wil
l not
>insert this field
to:
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
this will
If my theory is correct, the interface layer provided by your
programming environment will supply the brackets or quotes needed around
the column name NUM CARS and the insert will work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No it doesn't work. I decided to go ahead and use the EXECUTE command
instead. Thanks for your help though.
"Hugo Kornelis" wrote:

> On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:
>
> (snip)
> Hi luv2travel,
> I don't recognise the language you are using. But it's clear that you
> are not feeding it an ready-to-chew query. You are providing column
> names and data and the programming language or calling interface somehow
> transforms this into a query to feed to SQL Server.
> Two things you might try, both based on the idea that maybe this
> language "knows" that columns names with spaces must be bracketed or
> quoted and therefore adds extra brackets or quotes to those you already
> supplied:
> 1. Set up a profiler trace to capture the query actually sent to SQL
> Server. This will provide absolute certainty about what's going on
> behind the scenes.
> 2. Change this line:
> to:
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
> this will
> If my theory is correct, the interface layer provided by your
> programming environment will supply the brackets or quotes needed around
> the column name NUM CARS and the insert will work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||it looks like you are using php (just a guess)...if that is the case,
then you are going to have to escape the brackets (since they are both
regular expression reserved characters and used in php as well (for
array cells).
this is a long shot, but try:
vaFieldList.PutElement(lArrayI=ADndex, &(_variant_t("\[NUM CARS\]")));
if the statement works in sql analyzer then it is probably at the
script level...in which case you should at least tell us which one it
is and at most post the question in that group.
hth,
hans