ColdFusion form not submitting data to database

Question

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 1

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>

Answer 2

You need to add quotes for #form.hex# because it is a string value, not integer:

INSERT INTO color_codes
        (id ,color, hexvalue)
        VALUES
        (#form.id#, '#form.color#', '#form.hex#')

Actually, it looks like your id is a text field as well. Is this how it is set up in the database? You're not doing any verification that the value is numeric.... Often the ID is an auto-incrementing field, and not supplied by the user at all on insert.