wrapping the value in <@SQ> wont work if you are not using DirectDBMS
you need to set Quote Value to True to get rid of the {ts ...
On Sep 13, 2010, at 12:12 PM, WebDude wrote:
> 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.
Ben Johansen
http://www.webspinr.com
[email protected]
Phone: 360-597-3372
Mobile: 360-600-7775
----------------------------------------
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe witango-talk" in the body.
