I use TEXT ENDTEXT all the time too, but I moved away from the string substitution provided by <<>>.
I would imagine it is failing because you need single quotes around the <<vDOB>> like this:
Text to cCmd textmerge noshow flags 2 pretext 15
insert into mydatabase.dbo.employees(name,dob) values ('<<cName>>','<<vDOB>>')
endtext
This is how I would do it:
cName = 'RAFAEL' vDOB = NULL
Text to cCmd textmerge noshow flags 2 pretext 15
insert into mydatabase.dbo.employees(name,dob) values (?cName,?vDOB)
endtext
sqlexec(nHandle,cCmd)
Frank.
Frank Cazabon
On 13/06/2018 01:26 PM, Rafael Copquin wrote:
Thank you. It works well as you suggested but:
cName = 'RAFAEL' vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values (?cName,?vDOB)' sqlexec(nHandle,cCmd)
the above works, the below construct does not:
Text to cCmd textmerge noshow flags 2 pretext 15
insert into mydatabase.dbo.employees(name,dob) values ('<<cName>>',<<vDOB>>)
endtext
sqlexec(nHandle,cCmd)
Why?
I use text.. endtext most of the time, especially when the statements are very long and occupy several lines.
Character strings are surrounded with '<<>>', numbers are <<>> and dates are '<<cDate>>' (dates are converted to the form YYY-MM-DD)
Rafael
2018-06-13 11:49 GMT-03:00 Frank Cazabon frank.cazabon@gmail.com:
Don't pass the dates as strings, use parameters and set the blank date parameter to null prior to sending it:
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULLENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon
On 13/06/2018 10:30 AM, Rafael Copquin wrote:
I have a SQL Server 2012 Express table with a field called DOB of type DATE
The field accepts NULL values and does not have a default value..
To insert the DOB from VFP I transform it to the form 'YYYY-MM-DD' and send it as a character string.
However, if the DOB is empty, the only way VFP inserts the record is if the empty value is sent as '' and the DOB field displays '1900-01-01' which is the value inserted.
I want to insert NULL in the field, not '1900-01-01'
If I use the SQL Server Management Studio, I can insert the NULL value directly with this expression:
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
However, this command, from VFP, does not insert the record:
cCmd = [insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL) ]
sqlexec(nHandle,cCmd)
How can I get the field to get the NULL value?
TIA Rafael Copquin
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]