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!

[-__cfSearching__-]

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

[Guest]

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.

[JR__Bob__Dobbs-qSBHQ2]

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.

[Guest]

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!

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

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>

[Guest]

That worked! I mean the [ ] worked. Thanks Mr Dobbs! Could you please tell me why this was an issue? is Size a reserved word in CF9 but not CF8? and why would DW automatically put the " " around Size anyway? just for that very reason? anyway thanks again!

[ilssac]

Size is a reserved word in Access, and probably other databases and it needs to be escaped. Whether you are using CF8 or CF9 has little to say about this.  What matters is the database driver.  The standard method to escape a field is with square brackets [].  Access allows an Access only way to escape a field by using double quotes "".  The driver used in CF8 must have allowed the Access only version.  The driver in CF9 must only allow the standard method.

[Guest]

Thanks Ian! Yes thats why I mentioned the fact that with Cf 9 I was forced to use the ACCESS unicode driver. Other than the switch to CF 9 from 8 thats the only other element that had changed. As for the Access reserved words, again I am using someone else's database, so its not like I could change that part either. I think I have everything working now. I guess I can chalk this experience up to learning what NOT to do when working with CF. Thanks again!

[ilssac]

To further you education a bit, you should be clear about what is a database restriction and what is a ColdFusion restriction.

This issue with access would likely apply to any technology you used to connect to this Access database, as long as they where using the same database drivers.

Also, all database systems have reserved words and one occasionally needs to use them, thus knowing how to properly escape these words when necessary is very useful.

The main trouble with Access (other then it not being a server base tool which is a whole other level of problem) is that it allows so much non-standard SQL that new developers may have trouble when they move on to enterprise systems such as MS SQL, MYSQL or Oracle to name three big database management systems.

[Guest]

I think I understand somewhat. If it were up to me, I would not choose to use access as our db. I have asked my instructor why we use it. He says in the spring they are going to start using mySQL, but that doesnt help me now.

So I guess I have to make do and hopefull if problems such as this arise, I can work through them or get help in forums like this to help me.

The Ben Forta books are very good and use sql as well. So hopefully by working through them I can learn more of what I actually need to be learning.

Thanks again Ian for the education!

[Dan_Bracuk]

Regarding,

Could you please tell me why this was an issue? is Size a reserved word in CF9 but not CF8?

My own observations are that when software gets upgraded, it gets less tolerant of less than perfect code.  I find this frustrating.  I'd prefer to have something crash when I write it, so that I can do something about it at the time.

These obseravations have occurred with ColdFusion, Oracle, and MSExcel upgrades.

[-__cfSearching__-]

My own observations are that when software gets upgraded,

it gets less tolerant of less than perfect code.  I find

this frustrating.  I'd prefer to have something crash when

I write it, so that I can do something about it at the

time.

I would be more concerned with using wizards that write non-standard code. I do not use Access (or DW) enough to know.. but AFAIK the standard escape characters are square brackets and not quotes.

[ilssac]

-==cfSearching==- wrote:

AFAIK the standard escape characters are square brackets and not quotes.

That is my understanding as well.

[Guest]

Well once again let me state. I AM a web designer learning colf fusion, not a programmer by trade. I am using what resourses I have been asked to use in class with regards to writing CF code for dynamic web templates. However, as I progress further in my CF learning, I do want to learn cf proficiently and will continue learning it after my class is over.(in 3 weeks)

[-__cfSearching__-]

It is not a dig at you. If using the wizards are a required part of your curriculum, then you are stuck with them for now. We are just pointing out that they are not the best way to learn good CF or SQL coding practices .. or of any language for that matter.

It is a common failing of most wizards. On the surface, they make it easy to perform certain tasks (usually). But they do not normally use the best coding practices while doing it. Case in point the addition of double quotes to the SQL. Wizards also tend to produce wildly unreadable and unmaintainable code. So the sooner you can stop using wizards, the easier it will be to start "really" learning the language.

-Leigh

[Guest]

I understand and I was not offended..lol And sorry if I sounda harsh in my reply. I appreciate any help you guys can give me! Like I said I am working through the Forta books own my own and of course while it uses DW, it doesnt have me working through the exercise using wizards of any kind. So I think that will help immensly.

[-__cfSearching__-]

No worries. People here are very helpful to anyone who wants to learn (and puts in some effort).

The CFWACK books are very good. If you do not have one already, you might also get a good book on SQL. As databases play a key role in most web applications. (As good as the CF books are, their main focus is obviously ColdFusion). So an extra book, focused on SQL specifically, would give you a good foundation IMO.

-Leigh

[ilssac]

-==cfSearching==- wrote:

(As good as the CF books are, their main focus is obviously ColdFusion). So an extra book, focused on SQL specifically, would give you a good foundation IMO

Which, if you like the writing of the main author of the CFWACK books, Ben Forta, he has also written the "Teach Yourself SQL in 10 minutes" for which I also here alot of praise.

[Guest]

Yes I remember seeing that book. I have his book "Teach yourself Regular Expressions" as well. I may look into the SQL book as well. Thanks!

[Guest]

Yes that is good advice. I will get some SQL books. I am taking a class in SQL in the spring as well, so that should help, provided we dont use a bunch of wizards I think..lol

[-__cfSearching__-]

... so that should

help, provided we dont use a bunch of wizards I think..lol

LOL ... maybe review a description of the course first?

[Guest]

I read the course description. lol How come I cant copy and paste here in this forum. anyway the course says its designed to teach SQL proficiency with an emphasis on data definition, manipulation, and control statements as well as report generations.

So seems like a good class! cant wait

Thanks

[-__cfSearching__-]

It looks like the forum removed some of the characters from

my post.

Mine as well. So apparently "[" and "]" characters are off-limits as well. Joy.

[Adam Cameron.]

teedoffnewbie wrote:

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.

OK, so first advice is if you're trying to learn CF: stop using all the wizards and what not, and code your templates by hand.  You will never get anywhere if you rely on the code that DW generates, and if it's anything like it used to be last time I used it, the code it generates is rubbish anyhow, so it's a very poor CF tutor.

Secondly, if you say you can't post your code because it "won't let you finish defining the query" (in the DW query wizard, yes?) then that's a DW problem, not a CF problem.  Anyhow, there's nothing to stop you NOT using that wizard and just coding the SQL by hand (which you should be doing if you're a learner anyhow, IMO).

--

Adam