Re: invalid parameter binding?
Take the comments out. I suspect CF is sending the SQL as one line of text, so the comments are blitzing the latter part of the SQL, along with the parameter placeholder, so you're binding one more param than the JDBC driver thinks it needs. From memory (and cannot test @ the moment, sorry: don't have CF on this machine), that error is not saying what you're binding is invalid, it's that the number of bindings you're trying to make mismatches what the driver is expecting. -- Adam On 25 January 2013 19:45, Greg Morphis gmorp...@gmail.com wrote: Specifically it doesn't like this: case when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_refund, On Fri, Jan 25, 2013 at 1:31 PM, Greg Morphis gmorp...@gmail.com wrote: I removed this section of the query and it worked.. case when ( select 1 from conference_refund cr where ac.id = cr.attendee_conference_id and is_complete = 0 and active = 1 and (acp.date_received is not null or ac.payment_method = 'credit') ) is not null then 2 -- show process_refund when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_process_refund, so looks like back to the drawing board.. but why does it work without the cfqueryparam? and it works fine in SQL Mgmt Studio? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354086 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
I'm getting Invalid Parameter Binding on this function http://pastebin.com/xfh8uLVa The conf_id variable is a UUID string and if I put the value in the query and manually run it I get the results back.. But why am I getting this with cfqueryparam? I'm not sure what database you're using, but I don't think UUID fields are treated as generic strings. You might try using CF_SQL_IDSTAMP or CF_SQL_CHAR(36) for the CFSQLTYPE attribute. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354066 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
Thanks Dave! I've used varchar before and it was working earlier. I had to change the query around a bit and since then I've been getting this error. But I tried botd idstamp and char(36) and got the exact same error both times. I'm using SQL Server 2005. It works find in SQL Server Mgmt Studio Express and if I hardcode the query with an ID. Also, I'm using CF9 if that makes a difference. Anything else you can suggest? On Fri, Jan 25, 2013 at 12:32 PM, Dave Watts dwa...@figleaf.com wrote: I'm getting Invalid Parameter Binding on this function http://pastebin.com/xfh8uLVa The conf_id variable is a UUID string and if I put the value in the query and manually run it I get the results back.. But why am I getting this with cfqueryparam? I'm not sure what database you're using, but I don't think UUID fields are treated as generic strings. You might try using CF_SQL_IDSTAMP or CF_SQL_CHAR(36) for the CFSQLTYPE attribute. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354068 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
Also, using this works.. c.conferenceUID = '#arguments.conf_id#' So I'm not sure what the problem is with cfqueryparam On Fri, Jan 25, 2013 at 12:43 PM, Greg Morphis gmorp...@gmail.com wrote: Thanks Dave! I've used varchar before and it was working earlier. I had to change the query around a bit and since then I've been getting this error. But I tried botd idstamp and char(36) and got the exact same error both times. I'm using SQL Server 2005. It works find in SQL Server Mgmt Studio Express and if I hardcode the query with an ID. Also, I'm using CF9 if that makes a difference. Anything else you can suggest? On Fri, Jan 25, 2013 at 12:32 PM, Dave Watts dwa...@figleaf.com wrote: I'm getting Invalid Parameter Binding on this function http://pastebin.com/xfh8uLVa The conf_id variable is a UUID string and if I put the value in the query and manually run it I get the results back.. But why am I getting this with cfqueryparam? I'm not sure what database you're using, but I don't think UUID fields are treated as generic strings. You might try using CF_SQL_IDSTAMP or CF_SQL_CHAR(36) for the CFSQLTYPE attribute. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354069 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
What's also weird is that there's an ID field and the UID field. I didn't want to pass the ID around so the UID is passed via urls and forms. So prior to the viewConferenceAttendees function I pass the UID to get the ID. I can dump the resulting query object and 1 row is returned. All good. However when I change the viewConferenceAttendees to accept the ID as oppose to the UID I get the same error: Error Executing Database Query.[Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s). The error occurred in *C:\workforceatm\com\workforceatm\conferenceMain\conferenceMainGateway.cfc: line 546 * 544 : and c.id = cfqueryparam cfsqltype=cf_sql_integer value=#arguments.conf_id# / 545 : cfif structKeyExists(arguments,include_incomplete) and arguments.include_incomplete eq 1*546 :cfelse* 547 : and ac.is_complete = 1 548 : /cfif On Fri, Jan 25, 2013 at 12:47 PM, Greg Morphis gmorp...@gmail.com wrote: Also, using this works.. c.conferenceUID = '#arguments.conf_id#' So I'm not sure what the problem is with cfqueryparam On Fri, Jan 25, 2013 at 12:43 PM, Greg Morphis gmorp...@gmail.com wrote: Thanks Dave! I've used varchar before and it was working earlier. I had to change the query around a bit and since then I've been getting this error. But I tried botd idstamp and char(36) and got the exact same error both times. I'm using SQL Server 2005. It works find in SQL Server Mgmt Studio Express and if I hardcode the query with an ID. Also, I'm using CF9 if that makes a difference. Anything else you can suggest? On Fri, Jan 25, 2013 at 12:32 PM, Dave Watts dwa...@figleaf.com wrote: I'm getting Invalid Parameter Binding on this function http://pastebin.com/xfh8uLVa The conf_id variable is a UUID string and if I put the value in the query and manually run it I get the results back.. But why am I getting this with cfqueryparam? I'm not sure what database you're using, but I don't think UUID fields are treated as generic strings. You might try using CF_SQL_IDSTAMP or CF_SQL_CHAR(36) for the CFSQLTYPE attribute. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354070 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
Also, using this works.. c.conferenceUID = '#arguments.conf_id#' So I'm not sure what the problem is with cfqueryparam I think it's pretty clear - we're not specifying the right parameter type. How many characters are in the UUID? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354071 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
This is what the URL looks like /admin/conference/index.cfm?action=view_attendeesconf_id=6f1e0d6d-c35e-4ea2-9e24-39e0e02d442d I reworked the query to use the ID as opposed to the UID and I'm getting the same error. But again, if I remove cfqueryparam it works. cfif structKeyExists(arguments, conf_id) and isnumeric(arguments.conf_id) and c.id = cfqueryparam value=#arguments.conf_id# CFSQLType=cf_sql_numeric / /cfif -- the above gets me the invalid binding error cfif structKeyExists(arguments, conf_id) and isnumeric(arguments.conf_id) and c.id = #arguments.conf_id# /cfif -- but this works fine On Fri, Jan 25, 2013 at 1:05 PM, Dave Watts dwa...@figleaf.com wrote: Also, using this works.. c.conferenceUID = '#arguments.conf_id#' So I'm not sure what the problem is with cfqueryparam I think it's pretty clear - we're not specifying the right parameter type. How many characters are in the UUID? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354072 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
This is what the URL looks like /admin/conference/index.cfm?action=view_attendeesconf_id=6f1e0d6d-c35e-4ea2-9e24-39e0e02d442d I reworked the query to use the ID as opposed to the UID and I'm getting the same error. But again, if I remove cfqueryparam it works. Out of curiosity, what happens if you remove the TYPE attributes from your CFARGUMENT tags and use CFQUERYPARAM with either field? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354073 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
So, just this: and c.id = cfqueryparam value=#arguments.conf_id# / ? still get [Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s). On Fri, Jan 25, 2013 at 1:20 PM, Dave Watts dwa...@figleaf.com wrote: This is what the URL looks like /admin/conference/index.cfm?action=view_attendeesconf_id=6f1e0d6d-c35e-4ea2-9e24-39e0e02d442d I reworked the query to use the ID as opposed to the UID and I'm getting the same error. But again, if I remove cfqueryparam it works. Out of curiosity, what happens if you remove the TYPE attributes from your CFARGUMENT tags and use CFQUERYPARAM with either field? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354074 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
I removed this section of the query and it worked.. case when ( select 1 from conference_refund cr where ac.id = cr.attendee_conference_id and is_complete = 0 and active = 1 and (acp.date_received is not null or ac.payment_method = 'credit') ) is not null then 2 -- show process_refund when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_process_refund, so looks like back to the drawing board.. but why does it work without the cfqueryparam? and it works fine in SQL Mgmt Studio? On Fri, Jan 25, 2013 at 1:28 PM, Greg Morphis gmorp...@gmail.com wrote: So, just this: and c.id = cfqueryparam value=#arguments.conf_id# / ? still get [Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s). On Fri, Jan 25, 2013 at 1:20 PM, Dave Watts dwa...@figleaf.com wrote: This is what the URL looks like /admin/conference/index.cfm?action=view_attendeesconf_id=6f1e0d6d-c35e-4ea2-9e24-39e0e02d442d I reworked the query to use the ID as opposed to the UID and I'm getting the same error. But again, if I remove cfqueryparam it works. Out of curiosity, what happens if you remove the TYPE attributes from your CFARGUMENT tags and use CFQUERYPARAM with either field? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354075 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
Specifically it doesn't like this: case when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_refund, On Fri, Jan 25, 2013 at 1:31 PM, Greg Morphis gmorp...@gmail.com wrote: I removed this section of the query and it worked.. case when ( select 1 from conference_refund cr where ac.id = cr.attendee_conference_id and is_complete = 0 and active = 1 and (acp.date_received is not null or ac.payment_method = 'credit') ) is not null then 2 -- show process_refund when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_process_refund, so looks like back to the drawing board.. but why does it work without the cfqueryparam? and it works fine in SQL Mgmt Studio? On Fri, Jan 25, 2013 at 1:28 PM, Greg Morphis gmorp...@gmail.com wrote: So, just this: and c.id = cfqueryparam value=#arguments.conf_id# / ? still get [Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s). On Fri, Jan 25, 2013 at 1:20 PM, Dave Watts dwa...@figleaf.com wrote: This is what the URL looks like /admin/conference/index.cfm?action=view_attendeesconf_id=6f1e0d6d-c35e-4ea2-9e24-39e0e02d442d I reworked the query to use the ID as opposed to the UID and I'm getting the same error. But again, if I remove cfqueryparam it works. Out of curiosity, what happens if you remove the TYPE attributes from your CFARGUMENT tags and use CFQUERYPARAM with either field? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354076 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
Specifically it doesn't like this: case when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_refund, OK, I think this is more complicated than I did before! You might want to try creating a stored procedure, then calling that from CF. That'll let you use parameters. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354077 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: invalid parameter binding?
What I did was just return the data_received value and I already was returning the payment_method so I just used CF code to check the values. Thanks for all your time Dave! On Fri, Jan 25, 2013 at 2:09 PM, Dave Watts dwa...@figleaf.com wrote: Specifically it doesn't like this: case when (acp.date_received is not null or ac.payment_method = 'credit') then 1 -- show init_refund else 0 -- show nada end as show_init_refund, OK, I think this is more complicated than I did before! You might want to try creating a stored procedure, then calling that from CF. That'll let you use parameters. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354078 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: Invalid parameter binding - again!
Don't put ' marks around CFQUERYPARAM ... it already handles that for you. Also, you were missing pound signs around several of the params. INSERT INTO dbo.Businesses ( CountyID, CityID, CategoryID, SubCatID, SubCat2ID, BusinessName, Address, Zip, Tele phone, Fax, Email, Website, TypeID, DateListed, Paid ) VALUES ( #county.countyid#, #form.city#, #form.combo0#, #form.combo1#, #form.combo2#, cfqueryparam value=#FORM.compname# cfsqltype=cf_sql_varchar maxlength=25, cfqueryparam value=#form.address# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#form.zip# cfsqltype=CF_SQL_VARCHAR, '#form.area#-#form.prefix#-#form.suffix#', '#form.farea#-#form.fprefix#-#form.fsuffix#', cfqueryparam value=#form.email# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#form.website# cfsqltype=CF_SQL_VARCHAR, 2, '#today#', 'No' ) ~| 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:325056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Invalid parameter binding - again!
Thanks. I did have it right, but I was trying different things to get it to work to the query got messed up. I did end up figuring it out though. -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: July-28-09 5:09 PM To: cf-talk Subject: re: Invalid parameter binding - again! Don't put ' marks around CFQUERYPARAM ... it already handles that for you. Also, you were missing pound signs around several of the params. INSERT INTO dbo.Businesses ( CountyID, CityID, CategoryID, SubCatID, SubCat2ID, BusinessName, Address, Zip, Tele phone, Fax, Email, Website, TypeID, DateListed, Paid ) VALUES ( #county.countyid#, #form.city#, #form.combo0#, #form.combo1#, #form.combo2#, cfqueryparam value=#FORM.compname# cfsqltype=cf_sql_varchar maxlength=25, cfqueryparam value=#form.address# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#form.zip# cfsqltype=CF_SQL_VARCHAR, '#form.area#-#form.prefix#-#form.suffix#', '#form.farea#-#form.fprefix#-#form.fsuffix#', cfqueryparam value=#form.email# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#form.website# cfsqltype=CF_SQL_VARCHAR, 2, '#today#', 'No' ) ~| 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:325057 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
