RE: CF and MySQL insering/updating database
You are replacing what is in that field. You will need to get the contents of that field and append the new values to it then update that column. At least that is what I see going on. HTH Clint -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: Wednesday, November 13, 2002 7:16 AM To: CF-Talk Subject: CF and MySQL insering/updating database Hi, Something must be wrong with my syntax. When a user goes into his preferences they get a list of files with check boxes which is being generated from my database. They have the option of clicking multiple boxes which gets inserted into field strOmit in the same database it got the fields strDisplay to display the list. If I select additional check boxes after inserting the initial ones and update the database it wipes the contents of field strOmit and inserts the new entries. I just want it to add (append) the new entires, not delete the field and insert. Whats the correct SQL statements? Here is just a snippet of my code. application.cfm cfset auth = #CGI.AUTH_USER# In this example auth = Bill index.cfm cfquery name=ListElement datasource=profiles dbtype=ODBC SELECT * FROMprofiles WHERE strUsername = '#auth#' /cfquery form action=add-action.cfm method=post ...code clipped... input type=checkbox name=select value=#i# ...code clipped... input type=submit value=Update class=navlinks /form add-action.cfm cfquery name=GetEnv datasource=profiles dbtype=ODBC SELECT * FROMprofiles WHERE strUsername = '#auth#' /cfquery cfquery name=UpdEnv datasource=profiles dbtype=ODBC UPDATE profiles SET strUsername = '#auth#', strOmit=cfqueryparam value=#FORM.select# cfsqltype=CF_SQL_LONGVARCHAR WHERE strUsername = '#auth#' /cfquery --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: CF and MySQL insering/updating database
Like... cfquery name=UpdEnv datasource=user-profiles dbtype=ODBC APPEND profiles SET strUsername = '#auth#', strOmit=cfqueryparam value=#FORM.select# cfsqltype=CF_SQL_LONGVARCHAR WHERE strUsername = '#auth#' /cfquery On Wed, 13 Nov 2002 07:23:40 -0600, Clint Tredway wrote: You are replacing what is in that field. You will need to get the contents of that field and append the new values to it then update that column. At least that is what I see going on. HTH Clint -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: Wednesday, November 13, 2002 7:16 AM To: CF-Talk Subject: CF and MySQL insering/updating database Hi, Something must be wrong with my syntax. When a user goes into his preferences they get a list of files with check boxes which is being generated from my database. They have the option of clicking multiple boxes which gets inserted into field strOmit in the same database it got the fields strDisplay to display the list. If I select additional check boxes after inserting the initial ones and update the database it wipes the contents of field strOmit and inserts the new entries. I just want it to add (append) the new entires, not delete the field and insert. Whats the correct SQL statements? Here is just a snippet of my code. application.cfm cfset auth = #CGI.AUTH_USER# In this example auth = Bill index.cfm cfquery name=ListElement datasource=profiles dbtype=ODBC SELECT * FROMprofiles WHERE strUsername = '#auth#' /cfquery form action=add-action.cfm method=post ...code clipped... input type=checkbox name=select value=#i# ...code clipped... input type=submit value=Update class=navlinks /form add-action.cfm cfquery name=GetEnv datasource=profiles dbtype=ODBC SELECT * FROMprofiles WHERE strUsername = '#auth#' /cfquery cfquery name=UpdEnv datasource=profiles dbtype=ODBC UPDATE profiles SET strUsername = '#auth#', strOmit=cfqueryparam value=#FORM.select# cfsqltype=CF_SQL_LONGVARCHAR WHERE strUsername = '#auth#' /cfquery --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: CF and MySQL insering/updating database
Nevermind...got it. cfset SelectStrOmit = ValueList(GetEnv.strOmit) cfset AppendStrOmit = ListAppend(SelectStrOmit,FORM.select) On Wed, 13 Nov 2002 07:23:40 -0600, Clint Tredway wrote: You are replacing what is in that field. You will need to get the contents of that field and append the new values to it then update that column. At least that is what I see going on. HTH Clint -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: Wednesday, November 13, 2002 7:16 AM To: CF-Talk Subject: CF and MySQL insering/updating database Hi, Something must be wrong with my syntax. When a user goes into his preferences they get a list of files with check boxes which is being generated from my database. They have the option of clicking multiple boxes which gets inserted into field strOmit in the same database it got the fields strDisplay to display the list. If I select additional check boxes after inserting the initial ones and update the database it wipes the contents of field strOmit and inserts the new entries. I just want it to add (append) the new entires, not delete the field and insert. Whats the correct SQL statements? Here is just a snippet of my code. application.cfm cfset auth = #CGI.AUTH_USER# In this example auth = Bill index.cfm cfquery name=ListElement datasource=profiles dbtype=ODBC SELECT * FROMprofiles WHERE strUsername = '#auth#' /cfquery form action=add-action.cfm method=post ...code clipped... input type=checkbox name=select value=#i# ...code clipped... input type=submit value=Update class=navlinks /form add-action.cfm cfquery name=GetEnv datasource=profiles dbtype=ODBC SELECT * FROMprofiles WHERE strUsername = '#auth#' /cfquery cfquery name=UpdEnv datasource=profiles dbtype=ODBC UPDATE profiles SET strUsername = '#auth#', strOmit=cfqueryparam value=#FORM.select# cfsqltype=CF_SQL_LONGVARCHAR WHERE strUsername = '#auth#' /cfquery --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
