>>>>>>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 ,
>
>I've seen it before, many people don't see the problems with the decimal point character used in other countries, which in VFP is determined by Set Point To and checked by Set('Point'). This is the best solution, as I see it:
>
cast(chrtran(mProperty ,',',set('Point')) as n(17,6))
This is one way.
I have business objects that simply change SET POINT TO while doing the transfers. (The next economist likes to have a whatever as a decimal point. Much more generic).
As stated before, the very best are german decimal points insert csv files. That's deadly.
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]