Using CF to Update SQL DB

[programmher]

I have a CF variable named #number#.

I want to update a SQL DB field named "record" to the value of #number#.

I have attempted the below update query:

<cfquery name=&quot;Renumber&quot; datasourceMyDatabase>

UPDATE dbo.Inventory
SET record = #form.number#
WHERE inventory_item= #Inventory_item#
</cfquery

I am trying to re-set the record numbers on my database to match the counted line items on my web form.

(Ex. - Item number InvItem RecordNum
1 laptops 1
2 keyboards 3
3 mousepads 4

I want the &quot;keyboard&quot; item to show a recorNum of 2 instead of 3. The SQL db datatype is a tinyint.

My query does not work. Using a <cfset> does not work.

What am I missing?!?!?

 

[GunJack]

Hey programmher,

The first thing to check is whether your database fields are numeric or text fields. If they are text fields, you'll need to surround your values with single quotes like this.

UPDATE dbo.Inventory
SET record = '#form.number#'
WHERE inventory_item= '#Inventory_item#'

When referencing text field values in comparisons, inserts, or updates, you have to use single quotes around the values. When doing the same with numeric or date field values, you use the values by themselves as your code currently does.

If this isn't it, I would then need to know if you get an error message or if it executes fine but the values don't get updated.

Hope this helps,
GJ

 

[CFHub]

Not sure if this is related to your problem, but I suspect that &quot;record&quot; is a reserved word.. and I bet &quot;number&quot; is too.

 

[programmher]

GunJack,

The SQL field I'm attempting to update is a tinyint field.

My queries are executing, and the values are updating but they are not correctly updating.

I am doing something wrong when querying the database...

I am trying your suggestion and double-checking my sql syntax.

 

[GunJack]

Hey programmher,

Try putting this right before your query:

<cfoutput>
SET record = #form.number#
WHERE inventory_item= #Inventory_item#
</cfoutput>

This will let you see how your query is getting sent in case your variables are not getting set correctly. The next thing I'll need to know is what values you send to the update statement (ie form.number, inventory_item) and what values show up in each field for the record you're updating.

Good luck,
GJ