> I am using Flash remoting to send an array to a CFC - works
> fine, until I try a query. I have been able to connect to the
> datasource and retrieve values in another CFC with no problem.
> Why am I getting a "Error Executing Database Query" error in Flash?
>
> The part that I don't understand is how to reference elements of an
> array (called myArray)that is being send to the CFC - this part of the
> query is probably the problem:
>
> VALUES (#myArray[i]#)
>
> But not sure how to fix. I can output myArray in the CFC and it works
> fine. I can also cfreturn the array to Flash and it also works.
>
> Here is the complete code:
>
> <cfcomponent>
> <cffunction name="recieveData" access="remote">
> <cfargument name="myArray" type="array" required="yes">
> <cfset mLen = ArrayLen(myArray)>
> <cfloop from="1" to=#mLen# index="i">
> <cfquery name="updateDB" datasource="coursesDB">
> INSERT INTO Lessons(ID, Lesson,
> Required, Time)
> VALUES (#myArray[i]#)
> </cfquery>
> </cfloop>
> <cfreturn "Success">
> </cffunction>
> </cfcomponent>
The reason you're getting that error is because you have more fields than
values in your INSERT statement:
INSERT INTO table (col1, col2, col3)
VALUES (val1, val2, val3)
There's nothing wrong with how you're referencing the array except that you
should explicitly scope it - Arguments.myArray. In addition, when you create
variables within your functions, they should be local variables in most
cases:
<cfset var mLen = ArrayLen(Arguments.myArray)>
In your case, you don't even need the local variable - just use
ArrayLen(Arguments.myArray) when you want to find the length of the array,
since you only need to find the value once.
Finally, I suspect that your array contains the three values for the four
fields ID, Lesson, Required and Time, so you'll want your array values to be
placed within the VALUES of your query rather than looping over the query
itself. You'll also want to use CFQUERYPARAM to build a prepared statement:
<cfargument ...>
<cfset var updateDB = ""> <!--- you want function variables to be local --->
<cfquery name="updateDB" datasource="coursesDB">
INSERT INTO Lessons (ID, Lesson, Required, Time)
VALUES (<cfqueryparam cfsqltype="cf_sql_integer"
value="#Arguments.myArray[1]#">,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#Arguments.myArray[2]#">,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#Arguments.myArray[3]#">,
<cfqueryparam cfsqltype="cf_sql_integer"
value="#Arguments.myArray[4]#">)
</cfquery>
In the above example, I didn't know what datatypes to use, so I just assumed
they're all integers. If they're not, change the CFSQLTYPE attributes
appropriately.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211902
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
