>>>I'm writing a routine to copy date from a DBF to a SQL server database. Everything works fine except for text and memo fields where there is a ' as this is failing on the insert command. I'm using SQL insert and then a variable list where each variable is a field from the DBF table. Had a look online but my searches are just returning inserting from csv files and not the info I need.
>>>~M
>>
>>Can you share some of your code?
>>
>>Can you use parameters - in this case it would not be an issue. Otherwise you'll have to double the single quote.
>Here is the parameter and the strtran I use and then the insert statement in full. If I don't use the strtran then the insert fails for those records with a ' fails.
>
>...
>mproperty15 = ALLTRIM(STRTRAN(NTR_desc,"'",""))
>...
>LcInsert = "Insert into NominalTransaction(NominalBatchNum, NominalStage, NominalJournalNum, NominalTranType, NominalDate," + ;
> "NominalPeriod, NominalPeriodFirst, NominalPeriodLast, NominalRelease, NominalCostCentreCode, " + ;
> "NominalCode, NominalDaybook, NominalJournalRef, NominalValue, NominalJournalDesc, " + ;
> "NominalDesc, NominalPeriodInput, NominalMode, NominalMemo01, NominalLink, " + ;
> "InsertUser, UpdateUser" + ;
> ")" + ;
> " VALUES (" + mProperty01 + "," + mProperty02 + "," + mProperty03 + ",'" + mProperty04 + "','" + mProperty05 + "'," + ;
> mProperty06 + "," + mProperty07 + "," + mProperty08 + "," + mProperty09 + ",'" + mProperty10 + "','" + ;
> mProperty11 + "','" + mProperty12 + "','" + mProperty13 + "'," + mProperty14 + ",'" + mProperty15 + "','" + ;
> mProperty16 + "'," + mProperty17 + "," + mProperty18 + ",'" + mProperty19 + "'," + mProperty20 + ",'" + ;
> mnomuser + "','" + mnomuser + "'" + ;
> ")"
>Hi Mark
*This is not needed. mproperty01 = ALLTRIM(STRTRAN(NTR_desc,"'",""))
mproperty01 = ntr_desc
lcInsert = "insert into nominaltransaction(nominalbatchnum) values ( ?m.mProperty01 )"
Anything where you concatenate the data into the sql command is called SQL INJECTION. That practice leaves you open to SQL Injection Attacks. Besides, doing what I show above means you need not do any strtrans to sanitize the input.
