Can't Update Table With Form Data in SQL Statement

[Guest]

Hi. Attached is the zip file of the error and the 2 cfm pages.... selectstudents.cfm goes first.

THe issue I am having is that the database is not being updated when I run the editroster.cfm page, submitted from the selectstudents.cfm page. The students #FORM.Period#_Class column should be filled with the #URL.ClassID# if the checkbox is checked on the selectstudents.cfm page.

Thanks for the help! It's appreciated

[craigkaminsky]

It might be an issue with your WHERE clause. In looking at the selectstudents.cfm file, the data for your checkbox appears to be a string (AD_Username field) but the WHERE clause is built for evaluating integers.

Try: WHERE AD_Username IN <cfqueryparam value="#FORM.checkbox#" cfsqltype="cf_sql_varchar" list="true" />

This will properly format the list items for a varchar (or other string-based) field to something along the lines of:

WHERE AD_Username IN ('Item1','Item2','Item3',etc)

[Guest]

Thanks, I tried that, but now i get "Incorrect syntx near '@P1'... @P1 doesn't even exist in any table...

[craigkaminsky]

For a little debugging fun, try adding this at the very top of your edit page:

<cfdump var="#form#" />

<cfdump var="#url#" />

<cfabort/>

This will let you see your form-scoped and URL-scoped variables that are being passed into your page. It'll probably help a bit in uncovering the source of the issue.

[Guest]

With 2 test students checked, I get this:

The URL for my test page came out to this:

http://dev.intranet.lds.ldsenterprises.com/AITE_Test_System/secure/Management/ClassMgr/editroster.cfm?ClassID=87aea971-1c14-4c6b-aab2-d71a12241bcd&Period=A1

BTW, I removed the single quotes around #AD_Username# in the CF Checkbox values.

[craigkaminsky]

Couple of things to look over:

1. For a test, try this query

<cfquery name="Updateroster" datasource="AITE_Test_System">
UPDATE students
SET A1_Class = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.ClassID#" />
WHERE AD_Username IN <cfqueryparam cfsqltype="cf_sql_varcahr" value="#FORM.Checkbox#" list="true" />
</cfquery>

I think it might be helpful to see if a more hard-coded query works (removing the dynamic creation of the field name).

2. Are you able to turn on debugging for your CF server? If so, it's the best way to actually see the SQL that's being generated. I'm guessing you know how to turn this on and use it but if not, let me know

*Glad you removed the single quotes. I missed that when I was scanning the selectstudents.cfm file!

[Guest]

Hm... ok, now I get this (I attached the picture, as it was being resized).

[craigkaminsky]

Ugh. Sorry. I kept trying to edit my post but it didn't work.

One other option I meant to add in the last post:

<cftry>
<cfquery name="Updateroster" datasource="AITE_Test_System">
UPDATE students
SET A1_Class = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.ClassID#" />
WHERE AD_Username IN <cfqueryparam cfsqltype="cf_sql_varcahr" value="#FORM.Checkbox#" list="true" />
</cfquery>
<cfcatch type="any">
     <cfdump var="#cfcatch#" />
     <cfabort />
</cfcatch>
</cftry>

Sometimes I get better error details from cfcatch when hitting the wall with such problems

Waiting on the image file to get out of the queue (wasn't ignoring it!)

[craigkaminsky]

Uh, looks like I messed you up with a typo !!

It's <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.checkbox#" list="true" />

I misspelled varchar (look closely at the cfqueryparam in the screen shot). Sorry about that!! You should just be able to correct the spelling of varchar in that line/tag and re-run it.

Also meant to add that I believe the @P1 and @P2 are referring to the params of the cfqueryparam tag.

[Guest]

UM, OK. This is what I get with the output... (and I fixed the typo first)

http://idisk.mac.com/macwiz1220-Public/CFError.pdf

[craigkaminsky]

To verify, what are the column types for A1_Class and AD_Username?

[Guest]

They are both nvarchar(50)

when i run the query in SQL Manager, replacing the dynamic data with static data, it works fine...

[craigkaminsky]

I figured they were nvarchar or varchar but just wanted to make sure! Thanks.

One more test. Change the where clause to the following:

WHERE Ad_Username IN (#ListQualify(Form.Checkbox,"'")#)

[Guest]

OK. I get

Missing argument name.

When using named parameters to a function, every parameter must have a name. The CFML compiler was processing: An expression beginning with ListQualify, on line 17, column 24.This message is usually caused by a problem in the expressions structure. The body of a cfquery tag beginning on line 14, column 2. The body of a cfquery tag beginning on line 14, column 2.

The error occurred in D:\program files\Apache Software Foundation\Apache2.2\dev.intranet\AITE_Test_System\secure\Management\ClassMgr\editroster.cfm: line 17

15 : UPDATE students 16 : SET A1_Class = <cfqueryparam cfsqltype="cf_sql_varchar" value="#URL.ClassID#" /> 17 : WHERE Ad_Username IN (#ListQualify(Form.Checkbox,')#) 18 : </cfquery> 19 : <cfcatch type="any">

[craigkaminsky · Accepted solution]

I swear I may kill this forum's rich text editor before the day is done (it can't possibly be me ). It stripped part of the code from the ListQualify function. It should be

WHERE Ad_Username IN (#ListQualify(Form.Checkbox,"'")#)

The single quote was somehow unwrapped from the double quotes surrounding it.

[Dan_Bracuk]

Next time suggest that they use cfqueryparmam list="yes".

[craigkaminsky]

I made that suggestion in the first post I supplied. The OP still ran into problems, so I went with ListQualify after the cfqueryparam with the list=true failed so as to continue the troubleshooting.

[Guest]

That did it! Thank's so much! I really appreciate the help.