Trouble with fieldnames containing spaces RESOLVED

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trouble with fieldnames containing spaces RESOLVED

Author

Topic

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 16:22:43

Help! I need to write a SQL statement to INSERT a record into an Access table, which has an Autonumber field as its primary key. Sadly, many of the fieldnames contain one or more spaces in the name (eg. "field name").

When I try to write the insert statement with the fieldnames specified like this:
INSERT INTO tblOne ( fieldname1, fieldname2, field name 3 )
VALUES ( value1, value2, value3 )
the spaces in the last fieldname cause a problem.

When I try encasing the problem fieldname in brackets, like this:
INSERT INTO tblOne ( fieldname1, fieldname2, [field name 3] )
VALUES ( value1, value2, value3 )
SQL thinks I want to be prompted for a value.

When I try to use the fully-qualified name in the insert statement like this:
INSERT INTO tblOne ( fieldname1, fieldname2, tblOne.[fieldname 3] )
VALUES ( value1, value2, value3 )
I get a SQL syntax error.

Then I tried doing the insert without spelling out the fieldnames at all, like this:
INSERT INTO tblOne
VALUES ( value1, value2, value3 )
but I am required to provide a value for every field, including the autonumber field, which defeats the purpose of the autonumber.

Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 16:25:40

from Books Online:

Following are the types of delimiters used in Transact-SQL:

Quoted identifiers are delimited by double quotation marks ("): 
	SELECT * FROM "Blanks in Table Name"
 
Bracketed identifiers are delimited by brackets ([ ]): 
	SELECT * FROM [Blanks In Table Name]

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 16:28:41

True, this works with a SELECT statement, but not an INSERT INTO statement.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:36:12

Duplicate post..other one has answer

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98575

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:38:31

quote:
Originally posted by RBianca

When I try encasing the problem fieldname in brackets, like this:
INSERT INTO tblOne ( fieldname1, fieldname2, [field name 3] )
VALUES ( value1, value2, value3 )
SQL thinks I want to be prompted for a value.

When I try to use the fully-qualified name in the insert statement like this:
INSERT INTO tblOne ( fieldname1, fieldname2, tblOne.[fieldname 3] )
VALUES ( value1, value2, value3 )
I get a SQL syntax error.

As I pointed out in your other post... Look at the different in the two bolded lines. Which one is it? if you are missing a space in the second example..it will prompt. it is NOT simply because of the brackets, it is because the column name is either mispelled or doesn't exist.

Poor planning on your part does not constitute an emergency on my part.

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 16:44:46

[fieldname 3] was a typo in the post, but this is not an error that occurs in my real code.

As for the duplicate post, I wasn't sure if you would call this an Access question or a SQL question. Either way, the solution has not yet been reached.

Sorry for the confusion.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:49:44

I can't duplicate the issue in Access. If the field name exists and is spelled properly, the insert statement works.

What is the actual syntax error Access returns?

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:51:48

If you do a select and a group by, you would theoretically get the same error (parameter error I presume)...I would create the column list in a access based design view query and just copy the column names. As I said, i can't duplicate the issue with a properly spelled and qualified field name.

Poor planning on your part does not constitute an emergency on my part.

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 17:10:35

Run-time error '3127':

The INSERT INTO statement contains the following unknown field
name: 'tblProduct_Codes!Confirm Fee'. Make sure you
have typed the name correctly, and try the operation again.

Here is a simplified example of the SQL statement generated:
INSERT INTO tblProduct_Codes ( Product_Code, Division_ID, Internal_Company_ID, [tblProduct_Codes]![Confirm Fee] ) VALUES ( '888888', 1, 15, '35325' )

I have tried substituting "Confirm Fee" with any of four dozen other fields with equally frustrating names. The same message comes back. The field name DEFINITELY exists in the table.
SELECT statements do not cause this problem, only INSERT statements.

If I write the INSERT INTO statement without specifying the field names at all, I run into trouble with the autonumber field. SQL is looking for a value for EVERY field, including the autonumber field, and won't automatically provide the next number. Can you think of a way around that?

Thanks,
RBianca

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 18:34:46

The site was down while i was trying to edit this

I copied your syntax and made a bogus table with your values and field names.

Using your syntax, I actually WAS able to reproduce the error (using INSERT VALUES()

Using this syntax I didn't


INSERT INTO tblProduct_Codes ( Product_Code, Division_ID, Internal_Company_ID, [Confirm Fee] )
SELECT tblProduct_Codes.Product_Code, tblProduct_Codes.Division_ID, tblProduct_Codes.Internal_Company_ID, tblProduct_Codes.[Confirm Fee]
FROM tblProduct_Codes;

and likewise no error here:


INSERT INTO tblProduct_Codes ( Product_Code, Division_ID, Internal_Company_ID, [Confirm Fee] )
SELECT '888888', 1, 15, '35325' ;

I don't get the error.

Do Insert / Select rather than Insert (Values)

Poor planning on your part does not constitute an emergency on my part.

someone lock one of these two identicial threads....LOL

Poor planning on your part does not constitute an emergency on my part.

RBianca
Starting Member

10 Posts

Posted - 2008-03-07 : 10:17:18

OMG - IT WORKS!!!!

Thank you thank you, dataguru1971, for sticking with me! I was beginning to think I was finished.

=)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources