How do I ignore reserved characters in an VB-SQL statement?

[nemsshred]

I am developing a database bridge between the Act! contact manager and an Access database using the Act! SDK. I am using VB 6.0 as the front end and am having all sorts of trouble with Act!'s unique id field. I want to use Act!'s unique id field as the link between the master records in Act! and the child records in my database. At this time, I am unable to run SQL commands on the unique id field because of some of the characters it contains. Here are some example unique id's stored in Act!:

/)"($ WA/5^
T'TC# @)!N]

Note the presence of the double quote in the first id and the single quote in the second id. These wacky ids are raising cain with my SQL statements. Basically, I want the program to treat all of the id's characters as one literal string so I can compare it to the ids stored in my database. Any suggestions for completing this task would be greatly appreciated.

Alex
NEMS

 

[AndyWatt]

I'd suggest parsing the field values as you build your query.

I use a function to do this:

Function SQLParse(strSource as String) As String
SQLParse = Replace(strSource, "'", "''&quot
SQLParse = Replace(SQLParse, "|", "\&quot
End Function


Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does my program keep showing error messages every time something goes wrong?"

 

[THEMAN101]

OK, that gets it to recoginze the enter key, now how do i display make a return in a textbox and write something while keeping the previous line still there?

 

[SemperFiDownUnda]

Many SQL engines accept two single quotes to be escaped into one single quote.

so if you have a value of T'TC# @)!N]
and want to build up a SQL string to goto another you could do something like this

dim sPrimaryKey as String
dim sSQL as string
sPrimaryKey = rsACT.Fields("PK_Contract").value 'primary key from your ACT db

sPrimaryKey = Replace(sPrimaryKey,"'","''")

sSQL = "INSERT INTO AccessTable " & vbnewline & _
       "       (FK_Contract, Blah, Yadda, something) " & vbnewline & _
       "VALUES ('" & sPrimaryKey & "', 'YAY','This Should work','Because 2 single quotes when sent to SQL will be interpreted as one single quote')"

Now if you want to display sPrimaryKey in a text box do it before you do the replace. Bascialy you do the replace JUST before building it into a SQL string. Its a conversion you only have to do on the way in.