[ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
This should go to the discussion list (and I redirected it there). Is the column set to allow nulls? If not, that's your issue. -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 cfquery 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 null. 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 -
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: re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
Woops, made a typo in the links, try these: http://cfdj.sys-con.com/read/41823.htm Part 1, Introduction http://cfdj.sys-con.com/read/41840.htm Part 2 http://cfdj.sys-con.com/read/41721.htm Part 3 Dan From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 28, 2006 1:06 PM To: discussion@acfug.org Subject: RE: re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table Jeff, Here are 3 links to a 3-part tutorial in ColdFusion Developers Journal on Stored Procedures: Using MS-SQL Stored Procedures with CF. I found them very helpful in understanding the basics of Stored Procedures, with plenty of code in the Listings of the article to get your feet wet. http://cfdj.sys-con.com/read/41823.htm Part 1, Introduction http://cfdj.sys-con.com/read/41840.htm Part 2 http://cfdj.sys-con.com/read/41840.htm Part 3 Dan Kaufman From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 12:48 PM To: discussion@acfug.org Subject: Re: re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table Thanks everyone for your help. The response was quick and useful. Nice to know there is some place I can turn for help since I'm the only web-dev at my company. This is pretty much what I came up with while waiting for a response. I was hoping there was something I was over looking that didn't involve as many cfif statements as this method is going to require but if it works, it works. My form contains about about 30 date and money fields. Hello copy and paste. I'm curious about the stored proc method. I've just started learning to use them but unfortunately this project is due tomorrow. Mischa Uppelschoten ext 10 [EMAIL PROTECTED] wrote: Try something like: if MyNewMoney EQ MyNewVal = NULL else MyNewVal = #form.MyNewMoney# CFQUERY... UPDATE MyTable SET MyNewMoney = #MyNewVal# Didn't test this, but the suggestion is to set a field to NULL using an assignment. I believe this is the only situation where you can use = NULL in SQL. Mischa. 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 - Mischa Uppelschoten The Banker's Exchange, Inc. 2020 Hills Avenue NW Atlanta, GA 30318 Phone: (404) 605-0100 ext. 10 Fax: (404) 355-7930 Web: www.BankersX.com Follow this link for Instant Web Chat: http://www.bankersx.com/Contact/chat.cfm?Queue=MUPPELSCHOTEN - To unsubscribe from this list, manage your profile
re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
Any reason to validate before the cfquery as some suggested or is it just personal preference vs doing in the SQL statement? ** Purely personal, I just like my SQL to look clean. If you have to do this for 30 fields, I'd write a quick function: cffunction name=CleanupVar returntype=string cfargument name=FormValue cfif FormValue EQ cfset NewVal = Null cfreturn x /cffunction and then call it like cfquery... UPDATE MyTable SET MySmallDateTime = #CleanupVar(form.SmallDateTimeValue)# I hope that makes sense. - 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: re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
Jeff, Here are 3 links to a 3-part tutorial in ColfFusion Developers Journal on Stored Procedures: Using MS-SQL Stored Procedures with CF. I found them very helpful in understanding the basics of Stored Procedures, with plenty of code in the Listings of the article to get your feet wet. http://cfdj.sys-con.com/read/41823.htm Part 1, Introduction http://cfdj.sys-con.com/read/41840.htm Part 2 http://cfdj.sys-con.com/read/41840.htm Part 3 Dan Kaufman From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, September 28, 2006 12:48 PM To: discussion@acfug.org Subject: Re: re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table Thanks everyone for your help. The response was quick and useful. Nice to know there is some place I can turn for help since I'm the only web-dev at my company. This is pretty much what I came up with while waiting for a response. I was hoping there was something I was over looking that didn't involve as many cfif statements as this method is going to require but if it works, it works. My form contains about about 30 date and money fields. Hello copy and paste. I'm curious about the stored proc method. I've just started learning to use them but unfortunately this project is due tomorrow. Mischa Uppelschoten ext 10 [EMAIL PROTECTED] wrote: Try something like: if MyNewMoney EQ MyNewVal = NULL else MyNewVal = #form.MyNewMoney# CFQUERY... UPDATE MyTable SET MyNewMoney = #MyNewVal# Didn't test this, but the suggestion is to set a field to NULL using an assignment. I believe this is the only situation where you can use = NULL in SQL. Mischa. 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 - Mischa Uppelschoten The Banker's Exchange, Inc. 2020 Hills Avenue NW Atlanta, GA 30318 Phone: (404) 605-0100 ext. 10 Fax: (404) 355-7930 Web: www.BankersX.com Follow this link for Instant Web Chat: http://www.bankersx.com/Contact/chat.cfm?Queue=MUPPELSCHOTEN - 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 - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates. - To unsubscribe from this list, manage your profile @ http://www.acfug.
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
Re: re[2]: [ACFUG Discuss] Re: [ACFUG Community] Quick (and hopefully simple) question about updating a SQL Server table
Think of validation like a pair of funnels (input/output) with business logic in the middle. Your data must pass through the funnel successfully on input before being put into the business logic. Data must be passed through a screen on output to ensure it doesn't contain any malicious metacharacters, such as or for XSS, on output. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] Dissent is the purest form of patriotism. --Thomas Jefferson On Sep 28, 2006, at 4:19 PM, Mischa Uppelschoten ext 10 wrote: Any reason to validate before the cfquery as some suggested or is it just personal preference vs doing in the SQL statement? ** Purely personal, I just like my SQL to look clean. If you have to do this for 30 fields, I'd write a quick function: cffunction name=CleanupVar returntype=string cfargument name=FormValue cfif FormValue EQ cfset NewVal = Null cfreturn x /cffunction and then call it like cfquery... UPDATE MyTable SET MySmallDateTime = #CleanupVar(form.SmallDateTimeValue)# I hope that makes sense. - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa 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 - - 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 -
