RE: cfinsert multiple records into DB
First the CFinsert should be avoided, it limits you. Writing your own SQL is easy. I would replace the value of the option in the player select with the ID not the First and Last name. You can delete your second select since the ID represents the First and Last name. cfoutput query=contacts option value=#contacts.contactID##contacts.FirstName# #contacts.LastName#/option /cfoutput Then before you do your insert query the FirstName and LastName with the ID then do your insert. These queries are assuming your contactid in the database is numeric datatype and the contactID represents the first and last name. action page: cfquery datasource=mydatabase name=getFirstAndLast SELECT FirstName, LastName FROM player WHERE contactid = #FORM.contactID# /cfquery cfquery datasource=mydatabase name=insertPlayer INSERT player (contactID, player) VALUES (#FORM.contactID#, '#getFirstAndLast.FirstName# #getFirstAndLast.LastName#') /cfquery Try to always scope your variables by adding FORM. nameOfQuery. Etc. in front of your variables. This will create much more precise code for you in the future as you learn more. -Original Message- From: Barry Mcconaghey [mailto:bmcconag...@gmail.com] Sent: Thursday, February 11, 2010 9:47 AM To: cf-talk Subject: cfinsert multiple records into DB Hello Everybody. I'm trying to build a form that only has one cfselect or drop-down menu option on my website. Right now I have two cfselect but I would like to only display the first cfselect (player). The second cfselect (contactID) I would like to hide. The player selected in the first cfselect should match the second cfselect contactID and go to the action page. cfform name=AllPlayers action=AddPlayers.cfm cfselect name=player size=1 cfoutput query=contacts option value=#FirstName# #LastName##FirstName# #LastName#/option /cfoutput /cfselect cfselect name=contactid size=1 cfoutput query=contacts option value=#contactid#(#contactid#) #FirstName# #LastName#/option /cfoutput /cfselect /cfform Action page - addplayers.cfm cfinsert datasource=mydatabase tablename=player formfields=contactID, player Thanks, Barry ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330590 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfinsert multiple records into DB
Whoops, I noticed a bug in my code. You should use FORM.player not FORM.contactid in the two CFQuery's since the name of the select is player. -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Thursday, February 11, 2010 11:11 AM To: cf-talk Subject: RE: cfinsert multiple records into DB First the CFinsert should be avoided, it limits you. Writing your own SQL is easy. I would replace the value of the option in the player select with the ID not the First and Last name. You can delete your second select since the ID represents the First and Last name. cfoutput query=contacts option value=#contacts.contactID##contacts.FirstName# #contacts.LastName#/option /cfoutput Then before you do your insert query the FirstName and LastName with the ID then do your insert. These queries are assuming your contactid in the database is numeric datatype and the contactID represents the first and last name. action page: cfquery datasource=mydatabase name=getFirstAndLast SELECT FirstName, LastName FROM player WHERE contactid = #FORM.contactID# /cfquery cfquery datasource=mydatabase name=insertPlayer INSERT player (contactID, player) VALUES (#FORM.contactID#, '#getFirstAndLast.FirstName# #getFirstAndLast.LastName#') /cfquery Try to always scope your variables by adding FORM. nameOfQuery. Etc. in front of your variables. This will create much more precise code for you in the future as you learn more. -Original Message- From: Barry Mcconaghey [mailto:bmcconag...@gmail.com] Sent: Thursday, February 11, 2010 9:47 AM To: cf-talk Subject: cfinsert multiple records into DB Hello Everybody. I'm trying to build a form that only has one cfselect or drop-down menu option on my website. Right now I have two cfselect but I would like to only display the first cfselect (player). The second cfselect (contactID) I would like to hide. The player selected in the first cfselect should match the second cfselect contactID and go to the action page. cfform name=AllPlayers action=AddPlayers.cfm cfselect name=player size=1 cfoutput query=contacts option value=#FirstName# #LastName##FirstName# #LastName#/option /cfoutput /cfselect cfselect name=contactid size=1 cfoutput query=contacts option value=#contactid#(#contactid#) #FirstName# #LastName#/option /cfoutput /cfselect /cfform Action page - addplayers.cfm cfinsert datasource=mydatabase tablename=player formfields=contactID, player Thanks, Barry ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330591 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfinsert multiple records into DB
Hehe, another bug, your select query should be querying the contact table not the player table. HTH, and I have not confused the heck of you. Chad -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Thursday, February 11, 2010 11:15 AM To: cf-talk Subject: RE: cfinsert multiple records into DB Whoops, I noticed a bug in my code. You should use FORM.player not FORM.contactid in the two CFQuery's since the name of the select is player. -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Thursday, February 11, 2010 11:11 AM To: cf-talk Subject: RE: cfinsert multiple records into DB First the CFinsert should be avoided, it limits you. Writing your own SQL is easy. I would replace the value of the option in the player select with the ID not the First and Last name. You can delete your second select since the ID represents the First and Last name. cfoutput query=contacts option value=#contacts.contactID##contacts.FirstName# #contacts.LastName#/option /cfoutput Then before you do your insert query the FirstName and LastName with the ID then do your insert. These queries are assuming your contactid in the database is numeric datatype and the contactID represents the first and last name. action page: cfquery datasource=mydatabase name=getFirstAndLast SELECT FirstName, LastName FROM player WHERE contactid = #FORM.contactID# /cfquery cfquery datasource=mydatabase name=insertPlayer INSERT player (contactID, player) VALUES (#FORM.contactID#, '#getFirstAndLast.FirstName# #getFirstAndLast.LastName#') /cfquery Try to always scope your variables by adding FORM. nameOfQuery. Etc. in front of your variables. This will create much more precise code for you in the future as you learn more. -Original Message- From: Barry Mcconaghey [mailto:bmcconag...@gmail.com] Sent: Thursday, February 11, 2010 9:47 AM To: cf-talk Subject: cfinsert multiple records into DB Hello Everybody. I'm trying to build a form that only has one cfselect or drop-down menu option on my website. Right now I have two cfselect but I would like to only display the first cfselect (player). The second cfselect (contactID) I would like to hide. The player selected in the first cfselect should match the second cfselect contactID and go to the action page. cfform name=AllPlayers action=AddPlayers.cfm cfselect name=player size=1 cfoutput query=contacts option value=#FirstName# #LastName##FirstName# #LastName#/option /cfoutput /cfselect cfselect name=contactid size=1 cfoutput query=contacts option value=#contactid#(#contactid#) #FirstName# #LastName#/option /cfoutput /cfselect /cfform Action page - addplayers.cfm cfinsert datasource=mydatabase tablename=player formfields=contactID, player Thanks, Barry ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330592 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfinsert multiple records into DB
Thanks Chad! It worked. Question...how do I add more input fields. I would like to add this to my final code: input type=text size=40 name=carpool_location / Final code that works: cfselect name=player size=1 cfoutput query=contacts option value=#contacts.contactID##contacts.FirstName# #contacts.LastName#/option /cfoutput /cfselect Action page: cfquery datasource=mydb name=getFirstAndLast SELECT FirstName, LastName FROM contacts WHERE contactid = #FORM.player# /cfquery cfquery datasource=mydb name=insertPlayer INSERT player (contactID, player) VALUES (#FORM.player#,'#getFirstAndLast.FirstName# #getFirstAndLast.LastName#') /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330611 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfinsert multiple records into DB
Just keep adding them to the form then on your action page you get their value like this: #FORM.carpool_location# -Original Message- From: Barry Mcconaghey [mailto:bmcconag...@gmail.com] Sent: Thursday, February 11, 2010 2:07 PM To: cf-talk Subject: Re: cfinsert multiple records into DB Thanks Chad! It worked. Question...how do I add more input fields. I would like to add this to my final code: input type=text size=40 name=carpool_location / Final code that works: cfselect name=player size=1 cfoutput query=contacts option value=#contacts.contactID##contacts.FirstName# #contacts.LastName#/option /cfoutput /cfselect Action page: cfquery datasource=mydb name=getFirstAndLast SELECT FirstName, LastName FROM contacts WHERE contactid = #FORM.player# /cfquery cfquery datasource=mydb name=insertPlayer INSERT player (contactID, player) VALUES (#FORM.player#,'#getFirstAndLast.FirstName# #getFirstAndLast.LastName#') /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330612 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfinsert multiple records into DB
Final code that works: WHERE contactid = #FORM.player# I would also suggest using cfqueryparam for a variety of reasons, including some limited sql injection protection. http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330613 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfinsert multiple records into DB
I'm getting an error message. I added this inside my CFFORM input type=text size=40 name=carpool_location / Action page: cfquery datasource=mydb name=getFirstAndLast SELECT FirstName, LastName FROM contacts WHERE contactid = #FORM.player# /cfquery cfquery datasource=mydb name=insertPlayer INSERT player (contactID, player, carpool_location) VALUES (#FORM.player#,'#getFirstAndLast.FirstName# #getFirstAndLast.LastName#', #FORM.carpool_location#) /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330614 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfinsert multiple records into DB
Carpool_location is probably a string data type in the database? Strings need to be surrounded by single quotes. I would do like Leigh suggested. It is best practice to use cfqueryparam. You may need to adjust the cfsqltype to what you database fields are. I guessed numeric and varchar. cfquery datasource=mydb name=getFirstAndLast SELECT FirstName, LastName FROM contacts WHERE contactid = cfqueryparam cfsqltype=cf_sql_numeric value=#FORM.player# /cfquery cfquery datasource=mydb name=insertPlayer INSERT player (contactID, player, carpool_location) VALUES (cfqueryparam cfsqltype=cf_sql_numeric value=#FORM.player#, cfqueryparam cfsqltype=cf_sql_varchar value=#getFirstAndLast.FirstName# #getFirstAndLast.LastName#, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.carpool_location#) /cfquery -Original Message- From: Barry Mcconaghey [mailto:bmcconag...@gmail.com] Sent: Thursday, February 11, 2010 2:43 PM To: cf-talk Subject: Re: cfinsert multiple records into DB I'm getting an error message. I added this inside my CFFORM input type=text size=40 name=carpool_location / Action page: cfquery datasource=mydb name=getFirstAndLast SELECT FirstName, LastName FROM contacts WHERE contactid = #FORM.player# /cfquery cfquery datasource=mydb name=insertPlayer INSERT player (contactID, player, carpool_location) VALUES (#FORM.player#,'#getFirstAndLast.FirstName# #getFirstAndLast.LastName#', #FORM.carpool_location#) /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330616 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
