recordset errors

[Guest]

     Hi new poster here. Quick history of the issues I'm having. I was running CF 8 64bit with Vista and had the MS Access driver issue. SO I downloaded CF 9 32 bit version and can now define my datasource for an Access database. No problems there now so that's good. Now when I go and define a recordset for a select query, I add several records to the Select statement and for some reason a particular record I add, CF always puts this record name in parenthesis. i.e. "Size" My database did not have the parenthesis included, but CF adds them just on this record name. With CF 8 it didn't seem to be a problem, but now with version 9 it throws up an error message: adojni.ComException: invalid use of '.','!', or '()'. in a query expression tblSwimwear."Size" in Microsoft JET database engine code 3092

     I know this is a lot to digest, but I'm very confused as to what's going on here. Again CF 8 had no problem with the " " around Size. I can even go and remove the parenthesis around Size before I test the select query, and still get the error. If I leave out the Size record, I get no errors.

     I will add this so I make sure ';m covering all the bases, but when I defined my datasource, I had to use Microsoft Access Unicode as my driver. Don't know if that would make a difference, but still letting anyone who may can help know. Thanks in advance!

[Guest]

Thanks for the help and advice. Again as a student I am relegated to following the curriculum the school has for learning certain ways and skillsets. I am however,on my own, working through Ben Forta's book "Web Application Construction Kit" vol 1 so I am learning the "correct" way. The only issue I have is that I am forced to use ACCESS databases, which obvioulsy is not the best for CF, That said I have learned alot using it in school.

The only reason I said I couldnt post my code was that, yes we are using some wizards to build CF applications, and yes that may not be the "right" way to do it, but again I am just following the steps our instructor was showing us. I then figured out I could post code I had already set up in other queries of the same type. And since I was using CF 8 then...which had no issues with my code or DW code either one, I posted that. The problem only arose when I started using CF 9. So I may not neccessarily agree with your statement that it is structly a DW problem.

Anyway, again thanks for all the help!

[JR__Bob__Dobbs-qSBHQ2]

Does the error occur when the query containing Size is executed or when looping over a recordset containing a Size field?

Can you post your code?

[Adam Cameron.]

for some reason a particular record I add, CF always puts this record name in parenthesis. i.e. "Size" My database did not have the parenthesis included, but CF adds them just on this record name.

Are you saying you have something like this:

<cfquery>

select size from someTable

</cfquery>

And when that run, CF returns a DB error and the SQL displayed in the error message is this:

select [size] from someTable

Failing that, where else are you seeing this?

I had to use Microsoft Access Unicode as my driver. Don't know if that would make a difference,

Driver issues aside, you should not be using Access as the database for a server-based application.  Access is a desktop application: it is not intended to be used as a back-end DB server.

--

Adam

[Guest]

ok here we go. This is code from a previous query I did and was working fine when I was running CF8

<cfquery name="rs" datasource ="Swimwear">

SELECT tblSwimwear.Model, tblSwimwear.Style, tblSwimwear.Color, tblSwimwear."Size", tblSwimwear.QuantityInStock

FROM tblSwimwear

WHERE tblSwimwear.color = 'black'

ORDER BY tblSwimwear.Color

</cfquery>

As you can see the tblSwimwear."Size" is a record in my database and the quotes were put around the word Size by the query wizard in Dreamweaver. This query was set up in Dreamweaver running CF 8 and ran with no issues. I displayed the results in a dynamic table and everything worked great. The minute I installed CF 9, this query no longer runs and the error is displayed. When I view it in live data view within dreamweaver it says:

invalid use of '.', '!', or '()' in query expression tblSwimwear."Size"

[JR__Bob__Dobbs-qSBHQ2]

Does the page run correctly outside of Dreamweaver? Is it possible that Dreamweaver CS3 and ColdFusion 9 are not compatible or that the Access driver is not compatible with one or the other?

Try replacing tblSwimwear."Size" with tblSwimwear.[Size]

[Guest]

correction so as not to make this more confusing..lol datasource here should say swimsuit not Swimwear. Swimwear is a table within the database. sorry for the typo