Skip to main content
A
Anonymous
November 12, 2009
Answered

recordset errors

  • November 12, 2009
  • 3 replies
  • 3668 views

     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!

    This topic has been closed for replies.
    Correct answer JR__Bob__Dobbs-qSBHQ2

    I would love to do any of these suggestions, But this is a school assigment and he uses ACCESS for the databases we are using and so I cannot use mySQL. I have no problem with any of this at school, but again I would like to be able to complete these at home as well. CF8 I could. CF9 I cant.

    Maybe, I'm thinking I should just completely uninstall all coldfusion from my machine and try to dl again and go back to CF 8 32 bit version.

    JR "Bob" Dobbs wrote:

    Can you post the code that does not contain the Size field in the query?

    I agree with the poster who suspects that Size is a reserved word. 

    Your options are to:

    1. Rename the field in Access.

    2. Try escaping the fieldname in your query by escaping it with square braces.  Note that this works for MS SQL so I suspect, but am not sure, that it will also work in Access.

    SELECT Size

    FROM my_table

    3. Consider using another database such as MS SQL Server, MySQL, Derby, or another relational database.  Access is not a good database.

    not sure what you mean by escaping with square brackets. You mean place [ ] around Size? Please explain.

    Thanks for all the help!


    not sure what you mean by escaping with square brackets. You mean place [ ] around Size? Please explain.

    It looks like the forum removed some of the characters from my post.

    Try

    <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>

    3 replies

    A
    Adam Cameron.
    Inspiring
    November 12, 2009
    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

    A
    Anonymous
    November 12, 2009

    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"

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 12, 2009

    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]

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 12, 2009

    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?

    -
    -__cfSearching__-
    Inspiring
    November 12, 2009

    How are you "testing" this query ?

    What program are you using to "define a recordset"?

    Without knowing more my guess would be "Size" is a reserved word? Perhaps whatever program you are using to design your query is trying to escape the column name. So it can be used in a query. That is just a guess though.

    For MS SQL/Access the more common escape characters are:

    SELECT FROM Table

    Though if it is a problem with reserved words, the best solution is to not used them as table or column names. ie Rename

    A
    Anonymous
    November 12, 2009

    I am using Dreamweaver cs3 to build CFM templates

    . I use the recordset wizard to build my query.

    Again when I did the same thing using CF 8, CF 8 also "put" the quotation marks around the word Size(my DB record name) as well, but it was never a problem. In CF 8 I could either leave the quotes around the word, or if I wanted to, take them out. Either way worked with CF 8. CF 9 however doesn't let me do either one. It automatically, for some reason puts " " around Size and gives the error. When I remove the quote marks I still get the same error.

    Again sorry if I am not clear enough, I just started learning CF about 2 months ago.

    In response to the next poster, I cannot post my code since it wont let me finish defining the query.

    Using dreamweaver and using the developer edition of the CF server running locally on my machine. also can upload the cf templates to my schools web server running CF as well.

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    November 12, 2009

    Can you post the code that does not contain the Size field in the query?

    I agree with the poster who suspects that Size is a reserved word.

    Your options are to:

    1. Rename the field in Access.

    2. Try escaping the fieldname in your query by escaping it with square braces. Note that this works for MS SQL so I suspect, but am not sure, that it will also work in Access.

    SELECT

    FROM my_table

    3. Consider using another database such as MS SQL Server, MySQL, Derby, or another relational database. Access is not a good database.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices