pipe & SQL

I just found a nice little thing today (our DBA's didnt know about it either), you cant insert a pipe ( | ) directly with sql

ie

insert into tblMyTable ( myField ) values ( "|" )

so, easy enough to change it to chr(124) and everything works well.

My Q is,  How am i suposed to know that??  Is there some doc's / people know of what other things i cann't just go striaght out and add (of course " and ', but any other whacky ones )

Open for any input (on topic)! -

points for all!!!

Dave
LVL 34
flavoAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
Better explanation than I can provide:
http://support.microsoft.com/default.aspx?scid=kb;en-us;178070&Product=acc

Only the | character has this problem.
0
 
kiranghagCommented:
hi,
seems u have used double qoutes to delimit a string.
this is not allowed in ms-sql...double qoutes are used to name variable/columns which include space.
to enclose a string, u need to use single qoute  

i tried following command and it worked
insert into tblMyTable ( myField ) values ( '|' )

chr(124) refers to the ascii value of the character and that you can use anytime to refer any other character, not limited to special character

if u need to insert a single qoute itself, u need to insert two of them after each other...
e.g. '''' = a single qoute
0
 
flavoAuthor Commented:
opps.. Yeah i know that.

the SQL bit refers to MS Access general garden variety of SQL.. sorry for the mis-understanding.

What im really after is what else to i have to convert to Chr(int)???

I can do " and '

You could cay i kinda know how to use access, and  i am well aware of such issues... (see profile)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
flavoAuthor Commented:
>> insert into tblMyTable ( myField ) values ( '|' )

Didn't seem to in Access 97
0
 
flavoAuthor Commented:
good enough for me!
0
 
flavoAuthor Commented:
you can goto sleep now shane
0
 
shanesuebsahakarnCommented:
Not yet, I'm still working :(
0
 
flavoAuthor Commented:
It must be about 2:30am..
0
 
shanesuebsahakarnCommented:
3:30am, to be precise, but I have a data conversion that must be done by 9AM tomorrow morning and the data only arrived at 9PM.....
0
 
flavoAuthor Commented:
crazy!  I did 13hrs yesterday, that's enough (way too much considering i work for the gevernment.. that's 3weeks worth of work in 1 day)...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.