>>>>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 + "'" + ;
>> ")"
>>
>
>Gregory provided you with the correct way of using parameters. One possible gotcha - check out the types of the parameters, quite often VFP sends them wrongly (say, float for numeric, etc.) In those cases you can use CAST to correct type inside the ?mproperty, e.g.
>
>'cast(?mProperty as NUMERIC(17,0))'
Try this with german standard
SET POINT TO ,
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]