Professional Web Applications Themes

CF MX 6 WHERE Clause - Macromedia ColdFusion

Hi everyone! I keep getting the following error when I submit an update form for processing Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred in C:\CFusionMX\wwwroot\chisumrepair\leasing\w_edit_a ction.cfm: line 14 12 : distributor = '#form.distributor#', distContactName = '#form.distContactName#', WarrantyRegistration = '#form.warrantyRegistration#', Problem1 = '#form.Problem1#', 13 : Problem2 = '#form.Problem2#', Problem3 = '#problem3#', Problem4 = '#form.Problem4#', Status = '#form.status#', OpenClosed ='#form.openClosed#' 14 : where ID = #form.Issue_ID# 15 : </cfquery> I have the following hidden field on the Update form itself: <input type="hidden" name="Issue_ID" value="#qGetIssueDetail.ID#"> I have the action page query as follows: cfquery name= ...

  1. #1

    Default CF MX 6 WHERE Clause

    Hi everyone!
    I keep getting the following error when I submit an update form for processing

    Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver] Too few parameters. Expected 1. The error occurred in
    C:\CFusionMX\wwwroot\chisumrepair\leasing\w_edit_a ction.cfm: line 14

    12 : distributor = '#form.distributor#', distContactName =
    '#form.distContactName#',
    WarrantyRegistration = '#form.warrantyRegistration#', Problem1 =
    '#form.Problem1#',
    13 : Problem2 = '#form.Problem2#', Problem3 = '#problem3#', Problem4 =
    '#form.Problem4#',
    Status = '#form.status#', OpenClosed ='#form.openClosed#'
    14 : where ID = #form.Issue_ID#
    15 : </cfquery>

    I have the following hidden field on the Update form itself:

    <input type="hidden" name="Issue_ID" value="#qGetIssueDetail.ID#">

    I have the action page query as follows:

    cfquery name= "qUpdateIssue" datasource="chisumrepairdata">
    UPDATE WarrantyIssue
    SET distributor = '#form.distributor#', distContactName =
    '#form.distContactName#', WarrantyRegistration = '#form.warrantyRegistration#',
    Problem1 = '#form.Problem1#', Problem2 = '#form.Problem2#', Problem3 =
    '#problem3#', Problem4 = '#form.Problem4#', Status = '#form.status#',
    OpenClosed ='#form.openClosed#'
    WHERE ID = #form.Issue_ID#
    </cfquery>

    I am stuck on this. It looks like the hidden field is being passed to the
    action page but CF is not processing the variable. Any suggestions on how to
    fix this situation?

    Thanks,
    Jeremy


    Jeremy5431 Guest

  2. #2

    Default Re: WHERE Clause error

    The first thing you should do whenever you run into a problem with the query is
    to replace your <cfquery..> with a <cfoutput>, and your </cfquery> with a
    </cfoutput>. Then, resubmit to the page. Now, take the text that would have
    been passed into the cfquery tag and manually run it in your database. This
    will give you a much more helpful error message and will help you get your
    query running correctly. Then, once you've tweaked the query to work correctly,
    duplicate any changes you made to the actual code in the .cfm page. Now you can
    put the opening and closing cfquery tags in and try it again.

    Kronin555 Guest

  3. #3

    Default Re: WHERE Clause error

    Are you sure that qGetIssueDetail.ID is numeric?
    jdeline Guest

  4. #4

    Default Re: WHERE Clause error

    Originally posted by: jdeline
    Are you sure that qGetIssueDetail.ID is numeric?

    Yes, I have the ID field setup as an autonumber in Access. I can see the SQL
    information in the debugging info and it showing WHERE ID = 7

    so its passing the information but not processing. I have the form.ID
    surrounded in #'s. Im stumped.

    Jeremy5431 Guest

  5. #5

    Default Re: WHERE Clause error

    The problem you are getting is because ID is a reserved word, if you change the
    name of the field to something like ISSUE_ID on the access database and change
    your query to :-

    UPDATE WarrantyIssue
    SET distributor = '#form.distributor#', distContactName =
    '#form.distContactName#', WarrantyRegistration = '#form.warrantyRegistration#',
    Problem1 = '#form.Problem1#', Problem2 = '#form.Problem2#', Problem3 =
    '#problem3#', Problem4 = '#form.Problem4#', Status = '#form.status#',
    OpenClosed ='#form.openClosed#'
    WHERE ISSUE_ID = #form.Issue_ID#

    it should solve the problem

    Mike :)

    MikeDon Guest

  6. #6

    Default Re: WHERE Clause error

    Im starting to think that there is more worng that what I am posting. (keep in
    mind this is the first app I am developing from scratch) I tried Mikes
    suggestion about changing the ID to ISSUE_ID. It didn't work. I deleted the
    WHERE clause. (I know you are not supose to do this because it updates all the
    records) I got the same error on another line, so I deleted that line of code
    and it still does not work.

    I am supose to write the code like this?
    SET X = 'form.x'

    Does it matter if the values from form.x are select boxes or a radio group?

    Thanks again for your help!

    Jeremy
    PS. if it would help to post the full code I can do that or post a URL to the
    app



    Jeremy5431 Guest

  7. #7

    Default Re: WHERE Clause error

    You might want to comment out the UPDATE, then CFOUTPUT your form variables just to see just what values they actually contain.

    Phil
    paross1 Guest

  8. #8

    Default Re: WHERE Clause error

    Did you set up any fields in Access as Yes/No types? If so I would suggest
    (many may disagree) abandoning them. All they've ever done for me is cause
    huge headaches. You have to mess with them by supplying true/false or 0/1
    values. If I inherit an app with Yes/No's the first thing I do is convert them
    all to 1 character Text and plug in a Y or an N, much more visible too, IMO.
    Then change the app to supply 'Y's or 'N's at update time.

    JMGibson3 Guest

  9. #9

    Default Re: WHERE Clause error

    Thanks for all the help so far...

    I have tried everything that has been suggested. I still get the same error.
    To make things easier I commented out everything but one form field and the
    corrponding action code. I also left in the Where Clause. I keep getting the
    same error.

    When I do the CFoutput the data looks normal and right.

    Jeremy5431 Guest

  10. #10

    Default Re: WHERE Clause error

    "When I do the cfoutput the data looks normal and right"

    OK, now take the next step and cut the cfoutputted query, paste it into the Access query too, and run it.
    Kronin555 Guest

  11. #11

    Default Re: WHERE Clause error

    Originally posted by: Kronin555
    "When I do the cfoutput the data looks normal and right"

    OK, now take the next step and cut the cfoutputted query, paste it into the
    Access query tool, and run it.

    Ok I pasted the query into Access and ran it. It gave me a promt for
    information so I entered in the info and it says field uneditable.

    This is the first time I have tried to update a database. so what does that
    mean and how do I fix it? I have a insert record form that works just fine.

    Thanks again for your help

    Jeremy5431 Guest

  12. #12

    Default Re: WHERE Clause error

    I'm confused as to why you would get that error unless you were attempting to
    update the primary key column. I am assuming that the ISSUE_ID column would be
    the PK, and I don't see you are attempting to set that column to another value.

    Are you sure that distributor, distContactName, WarrantyRegistration,
    Problem1, Problem2, Problem3, Problem4, Status, and OpenClosed are actual
    column names in your WarrantyIssue table?

    Phil

    paross1 Guest

  13. #13

    Default Re: WHERE Clause error

    Good morning!

    Yes I am confused about this situation as well and I doubble checked my table
    and I do have column names for each of the names you listed.

    For the most part I have everything setup as text fields at this point except
    for the primary key, Problem1, Problem2, Problem3, Problem4, and status. I am
    doing this to test to make sure things are working. Should I switch the number
    columns to number fields and so on?

    I am thinking about switching the autonumber to a normal number field and
    writing CF code to automatically generate a new value for the primary key field
    for the insert form, but I would like to know why I cannot update a record
    containing an autonumber.

    Jeremy
    Originally posted by: paross1
    I'm confused as to why you would get that error unless you were attempting to
    update the primary key column. I am assuming that the ISSUE_ID column would be
    the PK, and I don't see where you are attempting to set that column to another
    value.

    Are you sure that distributor, distContactName, WarrantyRegistration,
    Problem1, Problem2, Problem3, Problem4, Status, and OpenClosed are actual
    column names in your WarrantyIssue table?

    Phil



    Jeremy5431 Guest

  14. #14

    Default Re: WHERE Clause error

    Hmmm, your original post didn't even show a dateOpened field, so am I to assume
    that this is the OpenClosed field? You need to make sure that your data types
    in your database match the parameters in the query, so if they are text, they
    should be enclosed in quotes, if they are numeric, they shouldn't be, and
    date/time fields should be matched to date/time objects, so you may need to use
    functions like CreateODBCDateTime(), etc. Or, if your dateOpened (or OpenClosed
    field, whatever) is text and you are attempting to set it to a date/time
    object, you may need to convert it to a char/text type first. Its all a matter
    of knowing what types you are dealing with in your database and using the
    appropriate values in your queries.

    As for your autonumber field, you should not have to change it, but you also
    should NOT be attempting to set its value from a query, nor should you attempt
    to insert a value into such a column.

    Phil

    paross1 Guest

Similar Threads

  1. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  2. Using variable in From clause
    By Glenn Stein in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 3rd, 08:45 PM
  3. help on join in from clause
    By Jen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:16 AM
  4. IN clause with 2 fields
    By Gustavo Pizzini Becker in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 09:07 PM
  5. update and having clause??
    By JT in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 07:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139