Benjadock Benjadock - 6 months ago 15
SQL Question

ColdFusion form not submitting data to database

I'm having some trouble with my ColdFusion code,
I have a simple form, and all I want it to do is to add the values to the database. I keep getting the error:
"error code 206
[Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: int is incompatible with text"

Here's my code:

<div class="form">
<form method="post" name="color">
ID:
<input type="text" name="id" id="id">
Color Name:
<input type="text" name="color" id="color">
Hex Value:
<input type="text" name="hex" id="hex">
<input type="submit" value="Submit">
</form>
</div>
<div>
<cfif structKeyExists(form, "color")>
<cfquery datasource="bentest" name="insertColor">
USE [benTest]

INSERT INTO color_codes
(id ,color, hexvalue)
VALUES
(#form.id#, '#form.color#', #form.hex#)
</cfquery>
<p>You've added a color to the database!</p>
</cfif>
</div>


And if I remove the single quotes from #form.color#, I get "Invalid column name '(what I entered)'."

Answer

If the hexvalue field in the database is a text field, e.g. nvarchar, varchar, text, then at a minimum you will need quotes around the variable... i.e.

<cfquery datasource="bentest" name="insertColor">
    USE [benTest]

    INSERT INTO color_codes (id ,color, hexvalue)
    VALUES (#form.id#, '#form.color#', '#form.hex#')
</cfquery>

However I strongly suggest you also cfqueryparam them too, i.e.

<cfquery datasource="bentest" name="insertColor">
    USE [benTest]

    insert into 
       [color_codes] (
          [Id], 
          [Color], 
          [HexValue]
    )
    values (
        <cfqueryparam value="#Form.Id#" cfquerytype="cf_sql_integer">,
        <cfqueryparam value="#Form.Color#" cfquerytype="cf_sql_varchar">,
        <cfqueryparam value="#Form.Hex#" cfquerytype="cf_sql_varchar">
    )
</cfquery>