W3Schools Forum

Full Version: Arrays

W3Schools Forum > Server Scripting > ColdFusion

vchris

Nov 7 2006, 03:01 PM

I have a form where unapproved users are displayed, could be 1 like it could be 100. Each user has a checkbox beside him so he can be approved easily.

The list returned by the check boxes is not really usable since it looks like this: test,test234.

I currently have this query:
UPDATE User_Table
SET Approved = 1
WHERE UserName IN ('#form.users#')

So what I want to do with this query is set the approve field to 1 for all checked users. The problem is the list of users needs to be between quotes so sql can update them. I tried converting the list to an array and maybe I would have more power over it but I still don't know how to update the users.

Any hints?

Skemcin

Nov 7 2006, 04:43 PM

so, #form.users# ends up being equal to t1,t2,t3,t4

and

you need it to be set to "t1","t2","t3","t4"

The quit and dirty way to do this is fairly simply.

<cfset form.users = "'0,#form.users#,0'">
<cfset form.users = "#replace(form.users,',','","','all')#">

First, recreate your variable so that it is in a format that you can use to globally replace certain characters.

Then, its hard to see in the forum font, but basically, replace all your commas with the quotation (one or two single quotes).

after this code, put in this:

<cfdump var="#form.users#">
<cfabort>

The <cfdump> tag, very useful, will output the value set at the time the dump tag is processed. You can place it wherever you want how ever many times you like - before and/or after just to see the changes if you like.

The <cfabort> tag stops all processes at that point - so its nice to stop database action from occurring before you are ready.

For more fun with <cfdump> set the var to #application#, #server#, #cookie#, #session#, #cgi# to see what other variables are at your disposal at any time.

Anyway, let me know if that helps.

vchris

Nov 7 2006, 04:51 PM

Here one way I found but I'll try yours after lunch.

<cfquery datasource="xxxxxxxx">
UPDATE User_Table
SET Approved = 1
WHERE UserName IN (
<cfset counter = 0>
<cfloop list="#form.users#" index="i">
<cfoutput>
<cfset counter = counter + 1>
'#i#'<cfif counter neq listLen(form.users)>,</cfif>
</cfoutput>
</cfloop>
)
</cfquery>

Skemcin

Nov 7 2006, 05:43 PM

ha - yeah that's the right way - I sometimes forget that the form values like that are considered arrays even though they dump like a string.

that is the better way to do it.

To make it neater, try this:

CODE

<cfparam name="counter" default="0">
<cfparam name="form.users" default="0">

<cfsavecontent variable="sqllist">
<cfloop list="#form.users#" index="i">
<cfoutput>
<cfset counter = counter + 1>
'#i#'<cfif counter neq listLen(form.users)>,</cfif>
</cfoutput>
</cfloop>
</cfsavecontent>

<cfquery name="qryUpdateUser" datasource="xxxxxx">
    UPDATE User_Table
    SET Approved = 1
    WHERE UserName IN (#sqllist#)
</cfquery>

vchris

Nov 7 2006, 05:55 PM

I wanted to replace loop in my query by a variable but then realized I can't do that for a loop. That cfsavecontent should be used a lot thanks for showing me this tag.

Why would I need <cfparam name="form.users" default="0"> ? I already have a failsafe. When nothing is checked then an error message is displayed.

EDIT: I get an error. Here is the query: UPDATE User_Table SET Approved = 1 WHERE UserName IN ( ''test'', ''test2'' ). It says the error is on this line: WHERE UserName IN (#sqllist#). I'm guessing it's the double quotes but when I simply do an output of sqllist I get 'test', 'test2' with single quotes. I don't get it.

Skemcin

Nov 8 2006, 02:52 PM

The <cfsavecontent> tag can be very useful - and I think you realize that - have fun with it!
:-D

I <cfparam> every variable I use on a page except those I am using to as a defined condition. That meaning, if #form.userid# is being used anywhere on the page, then I param it so that my page doesn't error out. Thats the main reason I param a variable - even if I have some other failsafe - I like being redundant. Plus, I did it in that example so I can run the code all by itself.

As far as the error - can you post or PM the exact error - rarely, they point to a line that is not the problem. If you are missing a # then it sometimes shows the error on the line/column of the next # since it thinks the variable never began or ended - depending on the way the code is written. Anyway, send me the url or a copy-n-paste of the error and I can see what you have.

Do you have debugging turned on? If so, you will see a all the actions the Cold Fusion server performs at the bottom of the page - after your site. You'll see every SQL statement run, all your session variables, all your CG variables, etc.

vchris

Nov 8 2006, 03:48 PM

I did an output of sqllist before the query and I get: 'test', 'test2' . Only single quotes! Somehow in the sql query it changes to double quotes. I don't see anything wrong other than double quotes.

Here is the error I get:

CODE

Error Occurred While Processing Request
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'test'.
 
The error occurred in D:\clients\GlDev\Pdbis\OECD\OECD\admin\approval.cfm: line 65

63 : 			UPDATE User_Table
64 : 			SET Approved = 1
65 : 			WHERE UserName IN (#sqllist#)
66 : 		</cfquery>
67 : 		<div class="formsuccess">Users Marked were approved successfully.</div>

SQL 	   UPDATE User_Table SET Approved = 1 WHERE UserName IN ( ''test'', ''test2'' )
DATASOURCE 	  xxxxxx
VENDORERRORCODE 	  102
SQLSTATE 	  42000
Please try the following:

    * Check the ColdFusion documentation to verify that you are using the correct syntax.
    * Search the Knowledge Base to find a solution to your problem.

Browser   	Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1) Gecko/20061010 Firefox/2.0
Remote Address   	142.135.216.100
Referrer   	http://greenlanedev3/pdbis/oecd/oecd/admin/approval.cfm
Date/Time   	08-Nov-06 10:46 AM
Stack Trace
at cfapproval2ecfm1987933592.runPage(D:\clients\GlDev\Pdbis\OECD\OECD\admin\approval.cfm:65) at cfapproval2ecfm1987933592.runPage(D:\clients\GlDev\Pdbis\OECD\OECD\admin\approval.cfm:65)

java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'test'.
	at macromedia.sequelink.ssp.Diagnostic.toSQLException(Unknown Source)
	at macromedia.sequelink.ssp.Chain.cnvDiagnostics(Unknown Source)
	at macromedia.sequelink.ssp.Chain.decodeDiagnostic(Unknown Source)
	at macromedia.sequelink.ssp.Chain.decodeBody(Unknown Source)
	at macromedia.sequelink.ssp.Chain.decode(Unknown Source)
	at macromedia.sequelink.ssp.Chain.send(Unknown Source)
	at macromedia.sequelink.ctxt.stmt.StatementContext.execDirect(Unknown Source)
	at macromedia.jdbc.sequelink.SequeLinkImplStatement.execute(Unknown Source)
	at macromedia.jdbc.slbase.BaseStatement.commonExecute(Unknown Source)
	at macromedia.jdbc.slbase.BaseStatement.executeInternal(Unknown Source)
	at macromedia.jdbc.slbase.BaseStatement.execute(Unknown Source)
	at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:212)
	at coldfusion.sql.Executive.executeQuery(Executive.java:974)
	at coldfusion.sql.Executive.executeQuery(Executive.java:886)
	at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236)
	at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447)
	at cfapproval2ecfm1987933592.runPage(D:\clients\GlDev\Pdbis\OECD\OECD\admin\approval.cfm:65)
	at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:147)
	at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357)
	at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62)
	at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107)
	at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
	at coldfusion.filter.PathFilter.invoke(PathFilter.java:80)
	at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47)
	at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52)
	at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilt
er.java:28)
	at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35)
	at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43)
	at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
	at coldfusion.CfmServlet.service(CfmServlet.java:105)
	at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
	at com.intergral.fusionreactor.filter.FusionReactorFilter.B(Unknown Source)
	at com.intergral.fusionreactor.filter.FusionReactorFilter.A(Unknown Source)
	at com.intergral.fusionreactor.filter.FusionReactorFilter.doFilter(Unknown Source)
	at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
	at jrun.servlet.FilterChain.service(FilterChain.java:101)
	at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
	at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
	at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:249)
	at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
	at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192)
	at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:318)
	at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:426)
	at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:264)
	at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

vchris

Nov 9 2006, 07:53 PM

Any ideas?

Skemcin

Nov 13 2006, 01:35 PM

post more of your source. The SQL that is generated looks like double single quotes as opposed to a single double quote. This could be a simple fix.

Sorry for not replying the last couple days - real busy over the weekend.

vchris

Nov 14 2006, 01:59 PM

There isn't much more to it but here it is:

CODE

<cfparam name="counter" default="0">
<cfparam name="form.users" default="0">

<cfelseif isDefined('form.approveMarked')>
<!--- At least 1 user is checked --->
<cfif isDefined('form.users')>
<cfsavecontent variable="sqllist">
<cfloop list="#form.users#" index="i">
<cfoutput>
<cfset counter = counter + 1>
'#i#'<cfif counter neq listLen(form.users)>,</cfif>
</cfoutput>
</cfloop>
</cfsavecontent>

<cfquery name="qryUpdateUser" datasource="xxxxxx">
    UPDATE User_Table
    SET Approved = 1
    WHERE UserName IN (#sqllist#)
</cfquery>
<div class="formsuccess">User(s) Marked were approved successfully.</div>
<cfelse>
<!--- If no selection --->
<div class="formfailure">User(s) not approved. No selection made.</div>
</cfif>

Skemcin

Nov 14 2006, 08:12 PM

you are starting out with a <cfelseif> you first must have a <cfif> before you can use a <cfelseif>. The elseif can only be nested inside a <cfif>,<cfelse>,</cfif> combination.

That might be all it is - let me know if it still errors out.

vchris

Nov 14 2006, 08:41 PM

I start with a <cfif>. It's another query which doesn't relate to this one so I didn't include it.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.