cfqueryparam

[Guest]

I am trying to process a form with multiple words entered with a single <cfinput type="text"...> tag. When I try and process the form, I get this error:

"Parameter 1 of function SetVariable, which is now text, must be a syntactically valid variable name"

on the line of the <cfqueryparam> tag in the update query. If I enter a sinlge word into the form element, I don't get an error. any recomendations.

[BreakawayPaul]

Can you post your actual code?

[Guest]

Sure!

Form field:

<CFFORM ACTION="processpage.cfm">

<cfinput type="text" 
              name="textentry"
              value="#Trim(GetContact.textentry)#">

</CFFORM>

process page:

<CFQUERY DATASOURCE="db">
                        UPDATE exampletable
                        SET Interest = <cfqueryparam value="#FORM.texentry#" cfsqltype="cf_sql_varchar">

</CFQUERY>

[BreakawayPaul]

Just talking a peek before bed, but I'll note that the name attribute is required for cfquery.

<cfquery name="myquery" datasource="db">

I don't know your db structure, but I'm used to seeing a WHERE clause with an update, otherwise you'll set every row in the table so that the "Interest" column equals your value.  If you only have one row, that's fine, or if you intend to update all the rows, that's fine.

If adding a name= to the query doesn't fix it, we'll look at things more closely.

[Guest]

I have added the name and I still have the seem problem. Interesting enough I haven't had issue with not naming the update query on somequery. Forgive me for not giving a the entire update statement. I do have a where statement. I am pretty sure  the problem exists in that line of the update statement. I have removed the cfqueryparam tag and get the same error... ugh. If I put a comma or an apostrophe, I get the same error. It will only take a string without spaces or delimeters. I want to allow the user to be able to put multple words and/or delimeters. I am using an access database with a text validation on that field. I have another that has a memo validation that does the same thing. I don't know that it is an access issue because I have done this before without issue. Beware of the next curveball. The data is updated into the db with the spaces or delimeter before it throws the error.

[BreakawayPaul]

Ok, I just did this test:

1) Created a table called "Test" in an Access database.

2) Created an ID column (autonumber) and a column called "item" (text).

3) Created the following snippet:

<cfset testdata = "This is a test">
<cfquery name="test" datasource="cftest">
UPDATE test
SET item = <cfqueryparam value="#testdata#" cfsqltype="cf_sql_varchar">
</cfquery>

With the empty database, I got no result (of course, since we're doing update and not insert), but once there was already a value, it worked with the spaces.

So make sure your table is starting off with some sort of value, and make sure that column in the database matches your data type (text, in this case).

[Guest]

AH, I think I see it. The error was the same but the line number was different. I am not sure why the error is the same. The error zeroed in on a <cfparam> tag.

Try <cfparam default="" name="form.item" type"string">

It has to do with the "type" attribute. Maybe the wrong attribute? If I get rid of the tag, it works.

[BreakawayPaul]

I've had cfsqltype= problems before.  If leaving that attribute off fixes it (that attribute is optional) then it's definitely something with that.  You could try a different type.  Whenever I get in a jam like that, I use this:

cfsqltype="cf_sql_clob"

That seems to work with just about anything.

[Guest]

It isn't the <cfqueryparam> tag it is in the <cfparam> tag. The "type" attribute is causing the problem. If I remove the cfparam tag the error goes away.

[BreakawayPaul]

Ahh, I see.  Interesting.  I've never used the type attribute for cfparam.

[Guest]

I think I am going to try and remove the type attribute and run with it from there. The default is what I am really looking to achieve. The type attribute may be redundant since I am using a cfqueryparam in the query. I really appreciate your input. Good brainstorming with you. Thanks!