Insert dynamic form fields into a database
I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database
All you need to do is loop through your form collection on the post_golfer_form.cfm page.Since your textboxes names are the golfers IDs, then you can use the textbox value to adjust the purse and the name of the textbox as the where clause.Like so: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# update golfers set golfer_purse = #evaluate(form.#thefield#) where golfer_id = #thefield# /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:38 AM To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database
If your database is case-insensitive or golfer_id is uppercase (or numeric): cfloop collection=#form# item=field cfquery datasource=#DSN# username=#db_username# password=#db_password# INSERT INTO tbl (golfer_id,golfer_purse) VALUES ( cfqueryparam cfsqltype=CF_SQL_CHAR value=#field#, cfqueryparam cfsqltype=CF_SQL_CHAR value=#form[field]# ) /cfquery /cfloop Else: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECTgolfer_id FROMgolfers /cfquery cfloop query=golfers cfquery datasource=#DSN# username=#db_username# password=#db_password# INSERT INTO tbl (golfer_id,golfer_purse) VALUES ( cfqueryparam cfsqltype=CF_SQL_CHAR value=#golfers.golfer_id#, cfqueryparam cfsqltype=CF_SQL_CHAR value=#form[golfers.golfer_id]# ) /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: donderdag 11 maart 2004 14:38 To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
Okay, I'm an idiot this morning. I don't understand what I'm supposed to be substituting. My code: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# UPDATE golfers SET golfer_purse = #evaluate(form.thefield) WHERE golfer_id = #form.# /cfquery /cfloop This needs to be populated from: input type=text name=#golfer_id# value=#golfer_purse# / Can this even work? -- Jillian -Original Message- From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED] Sent: March 11, 2004 7:46 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database All you need to do is loop through your form collection on the post_golfer_form.cfm page.Since your textboxes names are the golfers IDs, then you can use the textbox value to adjust the purse and the name of the textbox as the where clause.Like so: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# update golfers set golfer_purse = #evaluate(form.#thefield#) where golfer_id = #thefield# /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:38 AM To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
UPDATE golfers SET golfer_purse = '#form[thefield]#' WHERE golfer_id = #thefield# -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: donderdag 11 maart 2004 15:12 To: CF-Talk Subject: RE: Insert dynamic form fields into a database - More Help Needed Okay, I'm an idiot this morning. I don't understand what I'm supposed to be substituting. My code: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# UPDATE golfers SET golfer_purse = #evaluate(form.thefield) WHERE golfer_id = #form.# /cfquery /cfloop This needs to be populated from: input type=text name=#golfer_id# value=#golfer_purse# / Can this even work? -- Jillian -Original Message- From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED] Sent: March 11, 2004 7:46 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database All you need to do is loop through your form collection on the post_golfer_form.cfm page.Since your textboxes names are the golfers IDs, then you can use the textbox value to adjust the purse and the name of the textbox as the where clause.Like so: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# update golfers set golfer_purse = #evaluate(form.#thefield#) where golfer_id = #thefield# /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:38 AM To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database
Let me correct myself (see inline): Pascal -Original Message- From: Pascal Peters Sent: donderdag 11 maart 2004 14:58 To: CF-Talk Subject: RE: Insert dynamic form fields into a database If your database is case-insensitive or golfer_id is uppercase (or numeric): cfloop collection=#form# item=field Replace previous line by cfloop list=#form.fieldnames# index=field cfquery datasource=#DSN# username=#db_username# password=#db_password# INSERT INTO tbl (golfer_id,golfer_purse) VALUES ( cfqueryparam cfsqltype=CF_SQL_CHAR value=#field#, cfqueryparam cfsqltype=CF_SQL_CHAR value=#form[field]# ) /cfquery /cfloop Else: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECTgolfer_id FROMgolfers /cfquery cfloop query=golfers cfquery datasource=#DSN# username=#db_username# password=#db_password# INSERT INTO tbl (golfer_id,golfer_purse) VALUES ( cfqueryparam cfsqltype=CF_SQL_CHAR value=#golfers.golfer_id#, cfqueryparam cfsqltype=CF_SQL_CHAR value=#form[golfers.golfer_id]# ) /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: donderdag 11 maart 2004 14:38 To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
Sorry about that, I had a slight typo in my code.The query should be: UPDATE golfers SET golfer_purse = #evaluate(form.#thefield#)# WHERE golfer_id = #thefield# This is just one way of doing this.Others will tell you, no doubt, that evaluate is inefficient and shouldn't be used.Unless you've got thousands of golfers on the form page, you're not really going to notice any meaningful slow down by using evaluate. Also, as always, use cfqueryparam where appropriate. -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 9:12 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database - More Help Needed Okay, I'm an idiot this morning. I don't understand what I'm supposed to be substituting. My code: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# UPDATE golfers SET golfer_purse = #evaluate(form.thefield) WHERE golfer_id = #form.# /cfquery /cfloop This needs to be populated from: input type=text name=#golfer_id# value=#golfer_purse# / Can this even work? -- Jillian -Original Message- From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED] Sent: March 11, 2004 7:46 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database All you need to do is loop through your form collection on the post_golfer_form.cfm page.Since your textboxes names are the golfers IDs, then you can use the textbox value to adjust the purse and the name of the textbox as the where clause.Like so: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# update golfers set golfer_purse = #evaluate(form.#thefield#) where golfer_id = #thefield# /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:38 AM To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
From: Hagan, Ryan Mr UPDATE golfers SET golfer_purse = #evaluate(form.#thefield#)# WHERE golfer_id = #thefield# YUK! Ditch the Evaluate() SET golfer_purse = #form[thefield]# So much faster and cleaner [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
ok, I'll bite.hehe this should work...look ma, no evaluate!LOL! Good point though Ryan. SET golfer_purse = #form[#thefield#]# now, I'd add that you should be cautious about looping over form.fieldnames, you may, most likey will rather, have some form objects that are not part of the UPDATEs.I'd suggest repeating your query golfersupdate again and loop over that to build your UPDATEs. Doug -Original Message- From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 9:30 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database - More Help Needed Sorry about that, I had a slight typo in my code.The query should be: UPDATE golfers SET golfer_purse = #evaluate(form.#thefield#)# WHERE golfer_id = #thefield# This is just one way of doing this.Others will tell you, no doubt, that evaluate is inefficient and shouldn't be used.Unless you've got thousands of golfers on the form page, you're not really going to notice any meaningful slow down by using evaluate. Also, as always, use cfqueryparam where appropriate. -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 9:12 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database - More Help Needed Okay, I'm an idiot this morning. I don't understand what I'm supposed to be substituting. My code: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# UPDATE golfers SET golfer_purse = #evaluate(form.thefield) WHERE golfer_id = #form.# /cfquery /cfloop This needs to be populated from: input type=text name=#golfer_id# value=#golfer_purse# / Can this even work? -- Jillian -Original Message- From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED] Sent: March 11, 2004 7:46 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database All you need to do is loop through your form collection on the post_golfer_form.cfm page.Since your textboxes names are the golfers IDs, then you can use the textbox value to adjust the purse and the name of the textbox as the where clause.Like so: cfloop index=thefield list=#form.fieldnames# cfquery name=golfersupdate datasource=#DSN# username=#db_username# password=#db_password# update golfers set golfer_purse = #evaluate(form.#thefield#) where golfer_id = #thefield# /cfquery /cfloop -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:38 AM To: CF-Talk Subject: Insert dynamic form fields into a database I've got a form that is made up of a dynamic list of form fields.How do I write the SQL query so that it will loop over all of the fields to do the insert? My form: cfquery name=golfers datasource=#DSN# username=#db_username# password=#db_password# SELECT* FROMgolfers ORDER BY golfer_name /cfquery br / form name=team_info action="" method=post table border=0 cellpadding=1 cellspacing=0 width=98% align=center tr td class=tableheader width=125Golfer/td td class=tableheaderPurse/td /tr cfloop query=golfers tr td#golfer_initial# #golfer_name#/td tdinput type=text name=#golfer_id# value=#golfer_purse# //td /tr /cfloop tr tdinput type=submit value=Save Changes/td /tr /table /form _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
cfQueryparam! cfQueryparam! cfQueryparam! cfQueryparam! cfQueryparam! Marlon -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:47 AM To: CF-Talk Subject: RE: Insert dynamic form fields into a database - More Help Needed From: Hagan, Ryan Mr UPDATE golfers SET golfer_purse = #evaluate(form.#thefield#)# WHERE golfer_id = #thefield# YUK! Ditch the Evaluate() SET golfer_purse = #form[thefield]# So much faster and cleaner [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Insert dynamic form fields into a database - More Help Needed
From: Marlon Moyer cfQueryparam! cfQueryparam! cfQueryparam! cfQueryparam! cfQueryparam! To Camel Cap that properly, shouldn't it be cfQueryParam? g [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]