INSERT into SQL with special character in field

[Marc Vanzegbroeck]

Hi,

I know this is not the forum for this, but I know a lot of people use SQL

I have a field 'NN(005)' in a SQL-database and want to insert something, but get an error because there is a '(' and ')' in the field-name.

I have try with:

Code: Select all  Expand view

INSERT INTO ucn_hwy (NAME,POINT_TYPE,NN(005)) VALUES ('CL09_3','PRMODNIM','5.0')

and with

Code: Select all  Expand view

INSERT INTO ucn_hwy (NAME,POINT_TYPE,'NN(005)') VALUES ('CL09_3','PRMODNIM','5.0')

but get always an error.
Is the a way to insert into something into this field?
The easyest way is to rename the field, but I prefer to use this.

Thanks.

[Rick Lipkin]

Marc

Surround your field name with brackets :

[This and that field name]

Rick Lipkin

[Marc Vanzegbroeck]

Rick,

I doesn't work, same problem...

[Rick Lipkin]

Marc

This did not work ?

Rick Lipkin

Code: Select all  Expand view

INSERT INTO [ucn_hwy] ([NAME],[POINT_TYPE],[NN(005)]) VALUES ('CL09_3','PRMODNIM','5.0')
 

[Marc Vanzegbroeck]

Rick,

With your example I get this error


Without the brackets I had


As you can see, the brackets are not allowed.
First, I had only an error 'near '(005'

I also tried
INSERT INTO ucn_hwy (NAME,POINT_TYPE,[NN(005)]) VALUES ('CL09_3','PRMODNIM','5.0')

but then I had the error

[Rick Lipkin]

Marc

Forgive the dumb question .. you are plugging in the correct val types into your fields ? Make sure your numeric fields accept decimals .. and not just integers.

Rick Lipkin

[Marc Vanzegbroeck]

Rick,

All the fields are CHAR. Thats why I put '5.0' instead of 5.0 in it.
It als doesn't work if I want to write 'Test' in it...

[Enrico Maria Giordano]

Marc,

check if your field name is right for MySQL rules:

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

EMG

[Marc Vanzegbroeck]

Hi,

I have found the solution!!!
INSERT INTO ucn_hwy (NAME,POINT_TYPE,`NN(005)`) VALUES ('CL09_3','PRMODNIM','5.0')

[nageswaragunupudi]

I saw the post just now.
While for Microsoft products like Access, MSSql, DBase drivers, etc, we use [ ] for escaping field names we need to use ` character (see below tilde ~ ) in MySql.

So `NN(005)` works.

For this purpose we provided nice translates.

Code: Select all  Expand view

cSql := "SQL INSERT INTO ucn_hwy (NAME,POINT_TYPE,NN(005)) VALUES ('CL09_3','PRMODNIM','5.0')"
 

would get translated as
"INSERT INTO `ucn_hwy` (`NAME`,`POINT_TYPE`,`NN(005)`) VALUES ('CL09_3','PRMODNIM','5.0')" if you are using MySql
or
"INSERT INTO [ucn_hwy] ([NAME],[POINT_TYPE],[NN(005)]) VALUES ('CL09_3','PRMODNIM','5.0')" if you are using MsSql,Access,etc.

FWH knows if you are using MySql or MSSql, etc when you open ado connection using FW_OpenAdoConnection.

If you use FWH support for ADO, you will never go wrong, save time and even generate a portable code