VFP9, SqlServer and NULL values

[Rafael Copquin]

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
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAHM-jJ0KLxPunmWfTQLkntRcggfbJ0Sff4Kj7Fzrc8e+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Frank Cazabon]

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 = NULL

ENDIF

m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"


Frank.

Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/fc7a21a4-c78e-4a77-9aaf-***@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stu

[Rafael Copquin]

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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank date
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAHM-jJ3t+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Frank Cazabon]

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

Post by Rafael Copquin
cName = 'RAFAEL'
vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values
(?cName,?vDOB)'
sqlexec(nHandle,cCmd)
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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank date
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/321dae82-fed1-8ea3-37a7-***@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

NULL means "I don't know" and <<NULL>> in a textmerge returns nothing.

In my VFP 9, it returned .NULL. as the second argument, which no
server would understand :)

try:

SET NULLDISPLAY TO 'NULL'

before the textmerge.

Also, this might work better if you formatted the date you are
inserting using DTOS and wrapped it in single quotes.

On Wed, Jun 13, 2018 at 1:26 PM, Rafael Copquin

Post by Rafael Copquin
cName = 'RAFAEL'
vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values
(?cName,?vDOB)'
sqlexec(nHandle,cCmd)
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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank date
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4s36=yQhNM-a+58hM=GQtMsXk1QRGXpGNi63_+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Dave Crozier]

+1 for mapping dates using dtos()

Dave



---------------------------------------------------------------
This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.

Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any
attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.

It is the responsibility of the recipient to ensure that they have adequate virus protection.

Flexipol Packaging Ltd.
Unit 14 Bentwood Road
Carrs
Industrial Estate
Haslingden
Rossendale
Lancashire
BB4 5HH

Tel:[phone]
Fax: [phone]
www.Flexipol.co.uk
---------------------------------------------------------------

Terms & Conditions:

Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller
Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time

-----Original Message-----
From: ProFox <profox-***@leafe.com> On Behalf Of Ted Roche
Sent: 13 June 2018 18:49
To: ***@leafe.com
Subject: Re: VFP9, SqlServer and NULL values

NULL means "I don't know" and <<NULL>> in a textmerge returns nothing.

In my VFP 9, it returned .NULL. as the second argument, which no server would understand :)

try:

SET NULLDISPLAY TO 'NULL'

before the textmerge.

Also, this might work better if you formatted the date you are inserting using DTOS and wrapped it in single quotes.

Post by Rafael Copquin
cName = 'RAFAEL'
vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values
(?cName,?vDOB)'
sqlexec(nHandle,cCmd)
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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John
Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4s36=yQhNM-a+58hM=GQtMsXk1QRGXpGNi63_+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@EX2010-A-FPL.FPL.LOCAL
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

Too bad that a Date variable doesn't know enough about itself to present
that.

myDateString= myDate.ToString(myFormat)

now iterate through all of the formats to potentially see:

// Displays the following output by format:// d: 6/15/2008//
D: Sunday, June 15, 2008// f: Sunday, June 15, 2008 9:15 PM//
F: Sunday, June 15, 2008 9:15:07 PM// g: 6/15/2008 9:15
PM// G: 6/15/2008 9:15:07 PM// m: June 15// o:
2008-06-15T21:15:07.0000000// R: Sun, 15 Jun 2008 21:15:07 GMT//
s: 2008-06-15T21:15:07// t: 9:15 PM// T: 9:15:07
PM// u: 2008-06-15 21:15:07Z// U: Monday, June 16, 2008
4:15:07 AM// y: June, 2008

Post by Dave Crozier
+1 for mapping dates using dtos()
Dave
---------------------------------------------------------------
This communication and the information it contains is intended for the
person or organisation to whom it is addressed. Its contents are
confidential and may be protected in law. If you have received this e-mail
in error you must not copy, distribute or take any action in reliance on
it. Unauthorised use, copying or disclosure of any of it may be unlawful.
If you have received this message in error, please notify us immediately by
telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise
the risk of virus transmission through email and therefore any files sent
via e-mail will have been checked for known viruses. However, you are
advised to run your own virus check before opening any
attachments received as Flexipol Packaging Ltd will not in any event
accept any liability whatsoever once an e-mail and/or any attachment is
received.
It is the responsibility of the recipient to ensure that they have
adequate virus protection.
Flexipol Packaging Ltd.
Unit 14 Bentwood Road
Carrs
Industrial Estate
Haslingden
Rossendale
Lancashire
BB4 5HH
Tel:[phone]
Fax: [phone]
www.Flexipol.co.uk
---------------------------------------------------------------
Notwithstanding delivery and the passing of risk in the goods, the
property in the goods shall not pass to the buyer until the seller
Flexipol Packaging Ltd. ("The Company") has received in cash or cleared
funds payment in full of the price of the goods and all other goods agreed
to be sold by the seller to the buyer for which payment is then due. Until
such time as the property in the goods passes to the buyer, the buyer shall
hold the goods as the seller's fiduciary agent and bailee and keep the
goods separate from those of the buyer and third parties and properly
stored protected and insured and identified as the seller's property but
shall be entitled to resell or use the goods in the ordinary course of its
business. Until such time as the property in the goods passes to the buyer
the seller shall be entitled at any time
-----Original Message-----
Sent: 13 June 2018 18:49
Subject: Re: VFP9, SqlServer and NULL values
NULL means "I don't know" and <<NULL>> in a textmerge returns nothing.
In my VFP 9, it returned .NULL. as the second argument, which no server would understand :)
SET NULLDISPLAY TO 'NULL'
before the textmerge.
Also, this might work better if you formatted the date you are inserting
using DTOS and wrapped it in single quotes.

Post by Rafael Copquin
cName = 'RAFAEL'
vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values
(?cName,?vDOB)'
sqlexec(nHandle,cCmd)
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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMY+eDEth3=QSWApR6nogu8yw39Ah2NqbMjVhk+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Eric Selje]

/Too bad/ Hey, that's *exactly* what the DateTime static variable type in
FoxTypes <https://github.com/eselje/FoxTypes> is all about.

myDate = createObject("Date")
myDate.value = date()
? myDate.ToString() && 06/14/2018
? myDate.ToString('F') && June 14, 2018 00:00:00 AM
? myDate.ToString('D') && Thursday, June 14, 2018
(etc etc etc)

Eric

Post by Stephen Russell
Too bad that a Date variable doesn't know enough about itself to present
that.
myDateString= myDate.ToString(myFormat)
// Displays the following output by format:// d: 6/15/2008//
D: Sunday, June 15, 2008// f: Sunday, June 15, 2008 9:15 PM//
F: Sunday, June 15, 2008 9:15:07 PM// g: 6/15/2008 9:15
2008-06-15T21:15:07.0000000// R: Sun, 15 Jun 2008 21:15:07 GMT//
s: 2008-06-15T21:15:07// t: 9:15 PM// T: 9:15:07
PM// u: 2008-06-15 21:15:07Z// U: Monday, June 16, 2008
4:15:07 AM// y: June, 2008

Post by Dave Crozier
+1 for mapping dates using dtos()
Dave
---------------------------------------------------------------
This communication and the information it contains is intended for the
person or organisation to whom it is addressed. Its contents are
confidential and may be protected in law. If you have received this

e-mail

Post by Dave Crozier
in error you must not copy, distribute or take any action in reliance on
it. Unauthorised use, copying or disclosure of any of it may be unlawful.
If you have received this message in error, please notify us immediately

by

Post by Dave Crozier
telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise
the risk of virus transmission through email and therefore any files sent
via e-mail will have been checked for known viruses. However, you are
advised to run your own virus check before opening any
attachments received as Flexipol Packaging Ltd will not in any event
accept any liability whatsoever once an e-mail and/or any attachment is
received.
It is the responsibility of the recipient to ensure that they have
adequate virus protection.
Flexipol Packaging Ltd.
Unit 14 Bentwood Road
Carrs
Industrial Estate
Haslingden
Rossendale
Lancashire
BB4 5HH
Tel:[phone]
Fax: [phone]
www.Flexipol.co.uk
---------------------------------------------------------------
Notwithstanding delivery and the passing of risk in the goods, the
property in the goods shall not pass to the buyer until the seller
Flexipol Packaging Ltd. ("The Company") has received in cash or cleared
funds payment in full of the price of the goods and all other goods

agreed

Post by Dave Crozier
to be sold by the seller to the buyer for which payment is then due.

Until

Post by Dave Crozier
such time as the property in the goods passes to the buyer, the buyer

shall

Post by Dave Crozier
hold the goods as the seller's fiduciary agent and bailee and keep the
goods separate from those of the buyer and third parties and properly
stored protected and insured and identified as the seller's property but
shall be entitled to resell or use the goods in the ordinary course of

its

Post by Dave Crozier
business. Until such time as the property in the goods passes to the

buyer

Post by Dave Crozier
the seller shall be entitled at any time
-----Original Message-----
Sent: 13 June 2018 18:49
Subject: Re: VFP9, SqlServer and NULL values
NULL means "I don't know" and <<NULL>> in a textmerge returns nothing.
In my VFP 9, it returned .NULL. as the second argument, which no server
would understand :)
SET NULLDISPLAY TO 'NULL'
before the textmerge.
Also, this might work better if you formatted the date you are inserting
using DTOS and wrapped it in single quotes.
On Wed, Jun 13, 2018 at 1:26 PM, Rafael Copquin <

Post by Rafael Copquin
cName = 'RAFAEL'
vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values
(?cName,?vDOB)'
sqlexec(nHandle,cCmd)
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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAAwxvUmDs2Tzevu-WGe8Nxag6kj2gK1sMsVyN7U+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

I don't ever remember writing code like this in FP/VFP:
myDate = createObject("Date")

That is great that you can, and I bet it would have been easier to pass a
date to another language in a function.

Post by Eric Selje
/Too bad/ Hey, that's *exactly* what the DateTime static variable type in
FoxTypes <https://github.com/eselje/FoxTypes> is all about.
myDate = createObject("Date")
myDate.value = date()
? myDate.ToString() && 06/14/2018
? myDate.ToString('F') && June 14, 2018 00:00:00 AM
? myDate.ToString('D') && Thursday, June 14, 2018
(etc etc etc)
Eric

Post by Stephen Russell
Too bad that a Date variable doesn't know enough about itself to present
that.
myDateString= myDate.ToString(myFormat)
// Displays the following output by format:// d: 6/15/2008//
D: Sunday, June 15, 2008// f: Sunday, June 15, 2008 9:15 PM//
F: Sunday, June 15, 2008 9:15:07 PM// g: 6/15/2008 9:15
2008-06-15T21:15:07.0000000// R: Sun, 15 Jun 2008 21:15:07 GMT//
s: 2008-06-15T21:15:07// t: 9:15 PM// T: 9:15:07
PM// u: 2008-06-15 21:15:07Z// U: Monday, June 16, 2008
4:15:07 AM// y: June, 2008

Post by Dave Crozier
+1 for mapping dates using dtos()
Dave
---------------------------------------------------------------
This communication and the information it contains is intended for the
person or organisation to whom it is addressed. Its contents are
confidential and may be protected in law. If you have received this

e-mail

Post by Dave Crozier
in error you must not copy, distribute or take any action in reliance

on

Post by Stephen Russell

Post by Dave Crozier
it. Unauthorised use, copying or disclosure of any of it may be

unlawful.

Post by Stephen Russell

Post by Dave Crozier
If you have received this message in error, please notify us

immediately

Post by Stephen Russell
by

Post by Dave Crozier
telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to

minimise

Post by Stephen Russell

Post by Dave Crozier
the risk of virus transmission through email and therefore any files

sent

Post by Stephen Russell

Post by Dave Crozier
via e-mail will have been checked for known viruses. However, you are
advised to run your own virus check before opening any
attachments received as Flexipol Packaging Ltd will not in any event
accept any liability whatsoever once an e-mail and/or any attachment is
received.
It is the responsibility of the recipient to ensure that they have
adequate virus protection.
Flexipol Packaging Ltd.
Unit 14 Bentwood Road
Carrs
Industrial Estate
Haslingden
Rossendale
Lancashire
BB4 5HH
Tel:[phone]
Fax: [phone]
www.Flexipol.co.uk
---------------------------------------------------------------
Notwithstanding delivery and the passing of risk in the goods, the
property in the goods shall not pass to the buyer until the seller
Flexipol Packaging Ltd. ("The Company") has received in cash or cleared
funds payment in full of the price of the goods and all other goods

agreed

Post by Dave Crozier
to be sold by the seller to the buyer for which payment is then due.

Until

Post by Dave Crozier
such time as the property in the goods passes to the buyer, the buyer

shall

Post by Dave Crozier
hold the goods as the seller's fiduciary agent and bailee and keep the
goods separate from those of the buyer and third parties and properly
stored protected and insured and identified as the seller's property

but

Post by Stephen Russell

Post by Dave Crozier
shall be entitled to resell or use the goods in the ordinary course of

its

Post by Dave Crozier
business. Until such time as the property in the goods passes to the

buyer

Post by Dave Crozier
the seller shall be entitled at any time
-----Original Message-----
Sent: 13 June 2018 18:49
Subject: Re: VFP9, SqlServer and NULL values
NULL means "I don't know" and <<NULL>> in a textmerge returns nothing.
In my VFP 9, it returned .NULL. as the second argument, which no server
would understand :)
SET NULLDISPLAY TO 'NULL'
before the textmerge.
Also, this might work better if you formatted the date you are

inserting

Post by Stephen Russell

Post by Dave Crozier
using DTOS and wrapped it in single quotes.
On Wed, Jun 13, 2018 at 1:26 PM, Rafael Copquin <

Post by Rafael Copquin
cName = 'RAFAEL'
vDOB = NULL
cCmd = 'insert into mydatabase.dbo.employees(name,dob) values
(?cName,?vDOB)'
sqlexec(nHandle,cCmd)
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

Post by Frank Cazabon
Don't pass the dates as strings, use parameters and set the blank
PRIVATE myDate AS Date
myDate = DATE()
IF EMPTY(m.myDate)
m.myDate = NULL
ENDIF
m.lcSQL = "INSERT INTO myTable (myDateField) VALUES (?m.myDate)"
Frank.
Frank Cazabon

Post by Rafael Copquin
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

Post by Stephen Russell

Post by Dave Crozier

Post by Rafael Copquin

Post by Frank Cazabon

Post by Rafael Copquin
if the empty value is sent as ''
and the DOB field displays '1900-01-01' which is the value

inserted.

Post by Stephen Russell

Post by Dave Crozier

Post by Rafael Copquin

Post by Frank Cazabon

Post by Rafael Copquin
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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Fernando D. Bozzo]

Very nice! Adapter Pattern to the rescue :D

Post by Eric Selje
myDate = createObject("Date")
That is great that you can, and I bet it would have been easier to pass a
date to another language in a function.

Post by Eric Selje
/Too bad/ Hey, that's *exactly* what the DateTime static variable type in
FoxTypes <https://github.com/eselje/FoxTypes> is all about.
myDate = createObject("Date")
myDate.value = date()
? myDate.ToString() && 06/14/2018
? myDate.ToString('F') && June 14, 2018 00:00:00 AM
? myDate.ToString('D') && Thursday, June 14, 2018
(etc etc etc)
Eric

--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAGQ_Juk=3KThfKCG+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

In SSMS run this script
CREATE PROCEDURE newEmployee
-- Add the parameters for the stored procedure here
@name varchar(75)
, @dob datetime = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Insert into Employees values(@name, @dob)

END
GO

In your VP app:

cCmd = [exec newEmployee 'john Doe' ]

sqlexec(nHandle,cCmd)

From wherever you can do this

select * from Employees

You should see this:

name dob
john Doe NULL

To get the date in use this statement:
exec newEmployee 'Tommy Doe', '1997-01-05'

You will now see this:
name dob
john Doe NULL
Tommy Doe 1997-01-05 00:00:00.000

Let SQL do the work for you and just call that code for all your necessary
functionality in the future.

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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJ2xW9eBskKxns4rc1Te=***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Rafael Copquin]

Thank you Stephen
Very clear and exact

Rafael

Post by Stephen Russell
In SSMS run this script
CREATE PROCEDURE newEmployee
-- Add the parameters for the stored procedure here
@name varchar(75)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
END
GO
cCmd = [exec newEmployee 'john Doe' ]
sqlexec(nHandle,cCmd)

From wherever you can do this

select * from Employees
name dob
john Doe NULL
exec newEmployee 'Tommy Doe', '1997-01-05'
name dob
john Doe NULL
Tommy Doe 1997-01-05 00:00:00.000
Let SQL do the work for you and just call that code for all your necessary
functionality in the future.

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
insert into mydatabase.dbo.employees(name,dob) values( 'John Doe',NULL)
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAHM-jJ3-F3_e_fQmH1e6Jw+uQUS-***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Vince Teachout]

Post by Rafael Copquin
cCmd = [insert into mydatabase.dbo.employees(name,dob) values( 'John
Doe',NULL) ]
sqlexec(nHandle,cCmd)

messagbox(message())

What error do you get back?

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/ebc4566b-bd51-0ce4-898d-***@taconic.net
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.