Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
"Dean H. Saxe" [EMAIL PROTECTED] wrote: This should go to the discussion list (and I redirected it there).I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received.Is the column set to allow nulls? If not, that's your issue.Yes, it is set to accept nulls. The issue is the form passes a "" and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length.-dhsDean H. Saxe, CISSP, CEH[EMAIL PROTECTED]"If liberty means anything at all, it means the right to tell people what they do not want to hear."-- George Orwell, 1945On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors. Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink --To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserformFor more info, see http://www.acfug.org/mailinglistsArchive @ http://www.mail-archive.com/discussion%40acfug.org/List hosted by http://www.fusionlink.com- Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
RE: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
Do this: mySmallDateField = NULLIF(LTRIM(RTRIM(whateverValueWasPassed)), '') -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jeff HowardSent: Thursday, September 28, 2006 3:00 PMTo: discussion@acfug.orgSubject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table"Dean H. Saxe" [EMAIL PROTECTED] wrote: This should go to the discussion list (and I redirected it there). I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received.Is the column set to allow nulls? If not, that's your issue. Yes, it is set to accept nulls. The issue is the form passes a "" and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length.-dhsDean H. Saxe, CISSP, CEH[EMAIL PROTECTED]"If liberty means anything at all, it means the right to tell people what they do not want to hear."-- George Orwell, 1945On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors. Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink --To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserformFor more info, see http://www.acfug.org/mailinglistsArchive @ http://www.mail-archive.com/discussion%40acfug.org/List hosted by http://www.fusionlink.com- Do you Yahoo!?Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
To set the value as NULL, he will have to do that from a stored procedure or use BlueDragon v7.x to have the null data type.TeddyOn 9/28/06, Justin Haygood [EMAIL PROTECTED] wrote: Validate the data coming in. If the data passed in is """", set it to NULL instead. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 3:00 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table Dean H. Saxe [EMAIL PROTECTED] wrote: This should go to the discussion list (and I redirected it there). I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received. Is the column set to allow nulls? If not, that's your issue. Yes, it is set to accept nulls. The issue is the form passes a and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] If liberty means anything at all, it means the right to tell people what they do not want to hear. -- George Orwell, 1945 On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors. Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- cf_payne /Blog: http://cfpayne.wordpress.com/Atlanta CFUG: http://www.acfug.org - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink -
Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
You can pass the unquoted string NULL, as well. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] What is objectionable, what is dangerous about extremists is not that they are extreme, but that they are intolerant. -- Robert F. Kennedy, 1964 On Sep 28, 2006, at 3:28 PM, Teddy Payne wrote: To set the value as NULL, he will have to do that from a stored procedure or use BlueDragon v7.x to have the null data type. Teddy On 9/28/06, Justin Haygood [EMAIL PROTECTED] wrote: Validate the data coming in. If the data passed in is , set it to NULL instead. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 3:00 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table Dean H. Saxe [EMAIL PROTECTED] wrote: This should go to the discussion list (and I redirected it there). I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received. Is the column set to allow nulls? If not, that's your issue. Yes, it is set to accept nulls. The issue is the form passes a and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] If liberty means anything at all, it means the right to tell people what they do not want to hear. -- George Orwell, 1945 On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors. Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- cf_payne / Blog: http://cfpayne.wordpress.com/ Atlanta CFUG: http://www.acfug.org - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
That's what I did. Thanks.UPDATETableNameSET CFIF FORM.VendorPOAmt6 IS NOT "" Money= #FORM.Money#, CFELSE Money= NULL, /CFIF etc. etc.Any reason to validate before the cfquery as some suggested or is it just personal preference vs doing in the SQL statement?"Dean H. Saxe" [EMAIL PROTECTED] wrote: You can pass the unquoted string NULL, as well.-dhsDean H. Saxe, CISSP, CEH[EMAIL PROTECTED]"What is objectionable, what is dangerous about extremists is not that they are extreme, but that they are intolerant."-- Robert F. Kennedy, 1964On Sep 28, 2006, at 3:28 PM, Teddy Payne wrote: To set the value as NULL, he will have to do that from a stored procedure or use BlueDragon v7.x to have the null data type. Teddy On 9/28/06, Justin Haygood <[EMAIL PROTECTED]>wrote: Validate the data coming in. If the data passed in is """", set it to NULL instead. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 3:00 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table "Dean H. Saxe" <[EMAIL PROTECTED]>wrote: This should go to the discussion list (and I redirected it there). I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received. Is the column set to allow nulls? If not, that's your issue. Yes, it is set to accept nulls. The issue is the form passes a "" and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "If liberty means anything at all, it means the right to tell people what they do not want to hear." -- George Orwell, 1945 On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors.Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- Blog: http://cfpayne.wordpress.com/ Atlanta CFUG: http://www.acfug.org - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink --To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserformFor more info, see http://www.acfug.org/mailinglistsArchive @ http://www.mail-archive.com/discussion%40acfug.org/List hosted by http://www.fusionlink.com- Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://w
RE: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
Slight Modification... UPDATETableNameSET CFIF FORM.VendorPOAmt6 IS NOT "" Money=cfqueryparam cfsqltype="cf_sql_float" maxlength="20" value="#FORM.Money#", CFELSE Money= NULL, /CFIF etc. etc. John From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff HowardSent: Thursday, September 28, 2006 3:52 PMTo: discussion@acfug.orgSubject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table That's what I did. Thanks. UPDATETableNameSET CFIF FORM.VendorPOAmt6 IS NOT "" Money= #FORM.Money#, CFELSE Money= NULL, /CFIF etc. etc. Any reason to validate before the cfquery as some suggested or is it just personal preference vs doing in the SQL statement? "Dean H. Saxe" [EMAIL PROTECTED] wrote: You can pass the unquoted string NULL, as well.-dhsDean H. Saxe, CISSP, CEH[EMAIL PROTECTED]"What is objectionable, what is dangerous about extremists is not that they are extreme, but that they are intolerant."-- Robert F. Kennedy, 1964On Sep 28, 2006, at 3:28 PM, Teddy Payne wrote: To set the value as NULL, he will have to do that from a stored procedure or use BlueDragon v7.x to have the null data type. Teddy On 9/28/06, Justin Haygood <[EMAIL PROTECTED]>wrote: Validate the data coming in. If the data passed in is """", set it to NULL instead. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 3:00 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table "Dean H. Saxe" <[EMAIL PROTECTED]>wrote: This should go to the discussion list (and I redirected it there). I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received. Is the column set to allow nulls? If not, that's your issue. Yes, it is set to accept nulls. The issue is the form passes a "" and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "If liberty means anything at all, it means the right to tell people what they do not want to hear." -- George Orwell, 1945 On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors. Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- Blog: http://cfpayne.wordpress.com/ Atlanta CFUG: http://www.acfug.org - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, se
Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
UPDATETableNameSET Money=cfqueryparam cfsqltype=cf_sql_float maxlength=20 value=#FORM.Money# null=#FORM.VendorPOAmt6 IS '' # /DK On 9/28/06, John Mason [EMAIL PROTECTED] wrote: Slight Modification... UPDATETableNameSET CFIF FORM.VendorPOAmt6 IS NOT Money=cfqueryparam cfsqltype=cf_sql_float maxlength=20 value=#FORM.Money#, CFELSE Money= NULL, /CFIF etc. etc. John From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]] On Behalf Of Jeff HowardSent: Thursday, September 28, 2006 3:52 PMTo: discussion@acfug.orgSubject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table That's what I did. Thanks. UPDATETableNameSET CFIF FORM.VendorPOAmt6 IS NOT Money= #FORM.Money#, CFELSE Money= NULL, /CFIF etc. etc. Any reason to validate before the cfquery as some suggested or is it just personal preference vs doing in the SQL statement? Dean H. Saxe [EMAIL PROTECTED] wrote: You can pass the unquoted string NULL, as well.-dhsDean H. Saxe, CISSP, CEH[EMAIL PROTECTED]What is objectionable, what is dangerous about extremists is not that they are extreme, but that they are intolerant.-- Robert F. Kennedy, 1964On Sep 28, 2006, at 3:28 PM, Teddy Payne wrote: To set the value as NULL, he will have to do that from a stored procedure or use BlueDragon v7.x to have the null data type. Teddy On 9/28/06, Justin Haygood wrote: Validate the data coming in. If the data passed in is , set it to NULL instead. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 3:00 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table Dean H. Saxe wrote: This should go to the discussion list (and I redirected it there). I apologize, this is my first attempt at trying to ask a question to the ACFUG and I just replied to an email that I received. Is the column set to allow nulls? If not, that's your issue. Yes, it is set to accept nulls. The issue is the form passes a and when it tries to update a smalldate and a money field in the table it throws and error. I put the SQL statement generated in the CF error into Enterprise Manager and dissected it field by field. The error that I get from Enterprise Manager is invalid data type or length. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] If liberty means anything at all, it means the right to tell people what they do not want to hear. -- George Orwell, 1945 On Sep 28, 2006, at 2:36 PM, Jeff Howard wrote: I am trying to do an update to a table in SQL Server with and one of the updates to the record is to a column that has a date (smalldate type) and I'm trying to update it to . Seems like this should be simple but everything I've tried keeps throwing errors. Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink - -- Blog: http://cfpayne.wordpress.com/ Atlanta CFUG: http://www.acfug.org - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted
