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.
