>>>>TEXT TO sqlcommand TEXTMERGE NOSHOW
>>>> INSERT INTO Table1 VALUES('first','second')
>>>>ENDTEXT
>>>>
>>> Vladimer,
>>>
>>>This works! However when I chnge the above line to
>>>
>>>INSERT INTO Table1 FROM MEMVAR
>>>
>>Another way is to roll your own conversion, i.e. have a function which would return a proper string representation of anything:
>>
>>
TEXT TO sqlcommand TEXTMERGE NOSHOW
>> INSERT INTO Table1 VALUES(<<toString(mytable.field1)>>,<<toString(mytable.field2)>>)
>>ENDTEXT
>>
>>Formatting may be quite straightforward - you need quotation marks around the strings, numbers as they come out of str() function, but you may need to find out the proper format for logicals, dates, datetimes and memos. Memos may go as strings, but if they contain carriage returns and/or other nonprintables, this may be a problem.
>
>Dragan,
>I used something similar to the 2nd example above. For numeric variables it didn't appear they they needed a conversion. I just used <
> in the VALUES phrase. For the string variables, I just used <<'mytable.firld2'>> in the VALUES phrase.
Actually the apostrophes would go around the textmerge delimiters - otherwise you'd get the field alias and name sent. I figure you got that right in your code.
And yes, straight textmerge like that is OK for numbers and strings (although I'd rather do an alltrim() on strings, no need to send the trailing blanks over the wire), but for logicals and specially dates/datetimes the string representation as done by textmerge may not be what the database side would understand. For SQL server, you need to send 0 and 1 for .f. and .t.; for Access I assume it'd be 0 and -1. For dates and datetimes, it'd be very set("date") sensitive. My favorite function to transform a datetime to string (for VFP) is[{^]+Transform(Ttoc( tuExp,1), "@R 9999-99-99 99:99:99" )+ [}]I have a similar one for SQL server.
>I assume that the ?mvar1 is the way that ACCESS does macro-substitution instead of &mvar1.
Nope, that's a way of VFP talking to the ODBC driver. It works the same when you push values to SQL server, MySql or other.
