No. but you will need to do something like this:
Use the existing Update Action to update all other columns except the date in
question.
And then an If Action !len(date) (<@ARG date> is empty)
execute this DMBS statement:
UPDATE dbo.status SET site_eval_completed=NULL WHERE (ID=<@ARG
ID>)
Else
Update Action to set the date to <@ARG date>
Robert
From: WebDude [mailto:[email protected]]
Sent: Monday, September 13, 2010 3:35 PM
To: [email protected]
Subject: RE: Witango-Talk: Updating or Inserting NULL into MSSQL datetime field
I guess I am hosed then, eh?
_____
From: Robert Shubert [mailto:[email protected]]
Sent: Monday, September 13, 2010 2:19 PM
To: [email protected]
Subject: RE: Witango-Talk: Updating or Inserting NULL into MSSQL datetime field
I think I just remembered that you are using Tango2000….
I suspect that my notes will only apply to the newer SQL Generator found in 5.x
(possibly only 5.5).
Robert
From: WebDude [mailto:[email protected]]
Sent: Monday, September 13, 2010 3:13 PM
To: [email protected]
Subject: RE: Witango-Talk: Updating or Inserting NULL into MSSQL datetime field
Okay,
By the way... thanks for your help...
I gave this a shot and still have problems. It does not let me insert anything,
whether a date or not. I have this with a bunch of other fields, yet on an
update, I can only toggle between True or False for the Quote Value of the
whole insert, not just a particular column. Notice that the only field I have
set up as a datetime filed is site_eval_completed.
Also note in the debug, it shows...
site_eval_completed={ ts '2000-01-00 00:00:00' } - not sure why this is popping
in there...
The variable loads fine. I am using...
<@IFEMPTY <@ARG site_eval_completed>>
<@ASSIGN user$site_eval_completed “NULL”>
<@ELSE>
<@ASSIGN user$site_eval_completed“ <@SQ><@ARG
site_eval_completed><@SQ>”>
/@IF
It shows the variable loaded correctly in debug...
[ElseIf Action]
[50]
ElseIfUpdate
[Results Action]
[50]
Results4
[Changed Vars]
[50]
user$site_eval_completed=NULL
[Update Action]
[50]
Update1
[Query]
[50]
UPDATE dbo.status SET
IPMG=?,Country=?,Factory_Name=?,Factory_Address=?,Contact_Name=?,Contact_Details=?,Self_Eval_Submit_Date=?,Meet_Minimum_Req=?,Invoice_num_Date_Issued=?,Payment_Status=?,Print_Test_Kit_num_Date_Sent=?,Site_Eval_Schedule=?,site_eval_completed={
ts '2000-01-00 00:00:00' },site_eval_status='',openclosed=? WHERE (ID=3)
[BoundVals]
[50]
[v1='Shore To Shore'; v2='Central America'; v3='Shore To Shore Lacar, Ltda.';
v4='Boulevard Industrial Norte, No 440 Zona 4 de Mixco, El Naranjo, Guatemala
City, Guatemala 01057'; v5='Hugo Cambran/Cesar Montes'; v6='502-2422-0200
[email protected] [email protected]'; v7='07.24.2009';
v8='YES.'; v9='08.10.09, 0809-1003'; v10='Wire payment received 7.14.10';
v11='CN0097, 7.16.10, test sheets received 8.9'; v12='9.23.10'; v13='Open']
[Error]
[66]
241 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string. 22008
[Error]
[66]
-101 General error during data source operation.
Path:
/admin/
File Name:
status.taf
Position:
Update1
Class:
DBMS
Main Error Number:
241
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime
from character string.
22008
_____
Path:
/admin/
File Name:
status.taf
Position:
Update1
Class:
Internal
Main Error Number:
-101
General error during data source operation.
_____
From: Robert Shubert [mailto:[email protected]]
Sent: Monday, September 13, 2010 1:09 PM
To: [email protected]
Subject: RE: Witango-Talk: Updating or Inserting NULL into MSSQL datetime field
This is no problem
First, setup a variable as such:
<@IFEMPTY <@ARG date>>
<@ASSIGN request$sql_date_value “NULL”>
<@ELSE>
<@ASSIGN request$sql_date_value “<@SQ><@ARG DATE><@SQ>”>
</@IF>
Then use the variable in the Update SQL action (Insert and Select also work).
The trick is that you must set the Quote Value on this column to False. This
tells Witango to use the variable content as is (which explains the need for
the <@SQ>s) rather than attempting to format the date automatically.
Note that if the argument date is human enterable, you’ll want to do a few
checks on the value first to ensure that it’s a valid date, in the correct
format that your SQL server expects, etc. Explicitly stripping single-quotes
would be a good idea.
Also, this would effectively create the value ‘9/13/2010’ which most SQL
servers can deal with, however it is more appropriate to use native formats. If
I remember MS SQL properly date format is: YYYY-mm-ddTHH:MM:SS:mmm
Another good idea is to use the ODBC date literal format: { d ‘YYYY-mm-dd’ }
(this is what Witango converts dates to behind the scenes)
The point is that by turning off Quote Value, formatting of the value is turned
over to you and you must ensure that it is correct.
Robert
PS. The value of “NULL” does not get quoted.
From: WebDude [mailto:[email protected]]
Sent: Monday, September 13, 2010 1:46 PM
To: [email protected]
Subject: Witango-Talk: Updating or Inserting NULL into MSSQL datetime field
Hello,
Can this be done? I have a table where they need to sort by date. The column is
a datetime filed to sort dates correctly. we also need to be able to update the
date, delete the date (make null) or keep the date null on an insert. Keeping
the dat null on insert is fairly easy, but what if they want to sdelete the
date? Adding blank form field defaults to 1/1/1900 in the db. Can an insert of
NULL be written in SQL? I tried he obvious... <@IFEMPTY <@ARG
date>>NULL<@ELSE><@ARG date>>/@IF>, <@IFEMPTY <@ARG
date>>DbNull.value<@ELSE><@ARG date>>/@IF>, etc...
Has anyone done this before?
Thanks!
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
_____
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
----------------------------------------
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
