Skip to main content
C
cfsetNewbie
Known Participant
March 11, 2010
Question

cfquery qeustion, not sure about syntax

  • March 11, 2010
  • 2 replies
  • 3100 views

Hello;

Can someone tell me how to make this query work properly.
I need to make this query do 2 things in my where statement. one function is, any record with the number 2 in the categoryID field, is to be shown, I'm not sure how to write that. Here is my query:

<cfquery name="getMerch" datasource="#APPLICATION.dataSource#">
SELECT MerchName,MerchDescription, MerchPrice, MYFile, CategoryID
FROM Merchandise
WHERE merchID = #ATTRIBUTES.merchID# AND CategoryID = 2
</cfquery>

This part works fine: WHERE merchID = #ATTRIBUTES.merchID#

This part is wrong: AND CategoryID = 2

how would I write this? can anyone help?

thanks

    This topic has been closed for replies.

    2 replies

    A
    Anonymous
    March 12, 2010

    Just looking at the errors your getting makes me think, your DB fields merchID and CategoryID are not defined as numeric or integers (cant remember what access calls them.

    Can you tell us the data type for these fields set in access?

    I would stop using access, your not doing your self learning any favours, nor you applications scalibility.  If I were you, I would really consider using microsoft sql express.  Its free (not even access is free) and you will be learning a db tech (same tech as its bigger brothers) that is more valuable to you in the future.

    C
    cfsetNewbieAuthor
    Known Participant
    March 12, 2010

    merchID is AutoNumber

    MerchName is Text

    MerchDescription is Memo

    MerchPrice is Currency

    MYFile is Text

    CategoryID is Number

    In the Category Table

    CategoryID is Autonumber

    CatName is Text

    that is the table structure.

    I agree with you on sql, this site, they want access and it's driving me nuts.

    this last error, is because I'm trying to use currancy and am not entering it properly I think.

    B
    BuddWright
    Participating Frequently
    March 13, 2010

    Remove the lsCurrencyFormat() function -- ColdFusion will handle that for you, I believe.  You just need to pass in a number, without the dollar sign for a currency field.

    existdissolve
    existdissolve
    Inspiring
    March 11, 2010

    Define "working properly."  Are you not getting results at all, or just the wrong results?

    And just curious, but is merchID the primary key for the table "Merchandise" where "merchID" will always be a unique value?

    C
    cfsetNewbieAuthor
    Known Participant
    March 11, 2010

    merchID is the primary

    key for this table. CategoryID is from the category table, and I only want those records to be shown. If I don't have this part of the qury it all works nicely, but when I added my AND CategoryID = 2 it started throwing errors.

    How would I write this?

    ilssac
    ilssac
    Inspiring
    March 11, 2010

    It really helps when you say something "Started Throwing Errors" that you show and|or describe the errors it is throwing.  Otherwise we have to recrete your entire environment on some spare system we got lying around and run your code and see if we get the same error.

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