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>

ColdFusion form not submitting data to database

Sign up