Skip to main content
M
margo_ann
Known Participant
May 4, 2007
Question

Problem with WHERE clause

  • May 4, 2007
  • 4 replies
  • 599 views
I want users to be able to search a database by last name, when I add the WHERE clause to my SQL statement I receive the following error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in C:\Inetpub\wwwroot\spl_web_site\public_records\search_action.cfm: line 13

11 : SELECT customer_info.first_name, customer_info.last_name, rec_request.action
12 : FROM customer_info INNER JOIN rec_request ON customer_info.cust_id = rec_request.new_id
13 : WHERE customer_info.last_name = #FORM.last_name#
14 : </cfquery>
15 : <body>

Here is my code so far:

    This topic has been closed for replies.

    4 replies

    C
    cheftimo
    Inspiring
    May 7, 2007
    If cust_id is a number, the code should be
    WHERE customer_info.cust_id = #cust_id# (no single quotes).
    D
    Dan_Bracuk
    Inspiring
    May 5, 2007
    When you click on a link and go to a new page, your form is gone. That's your problem. Make them session variables and you should be fine.
    D
    Dan_Bracuk
    Inspiring
    May 5, 2007
    Pass the id, not the last name. Plus having form variables and url variables available on the same page seldom happens unless you set some of them yourself.
    M
    margo_annAuthor
    Known Participant
    May 5, 2007
    After I do my search I have a list of last names to choose from. When I choose a last name this is the url:

    http://localhost/spl_web_site/public_records/admin_edit.cfm?last_name=27

    I am still not sure what syntax to use in my WHERE clause to make sure record 27 is updated

    This is the code that links to the record when I choose one of the names on the resulting search list:

    <cfoutput query="search">
    <p>Date Requested: #DateFormat(date_received, "MMMM D, YYYY")#</p>
    <p>Last Name: <a href="admin_edit.cfm?last_name=#cust_id#">#last_name#</a></p>
    <p>First Name: #first_name#</p>
    <p>Action Taken: #action#</p>
    <hr>
    Here is the code for my update:
    UPDATE rec_request
    SET staff_lname='#form.staff_lname#',
    staff_fname='#form.staff_fname#',
    staff_ph_ext='#form.staff_ph_ext#'
    WHERE customer_info.cust_id = '#cust_id#'

    I am receiving receiving too few parameters (exact message follows)
    Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

    The error occurred in C:\Inetpub\wwwroot\spl_web_site\public_records\update.cfm: line 15

    13 : staff_fname='#form.staff_fname#',
    14 : staff_ph_ext='#form.staff_ph_ext#'
    15 : WHERE customer_info.cust_id = '#cust_id#'
    16 : </cfquery>
    17 : <body>

    Thank you.




    tclaremont
    tclaremont
    Inspiring
    May 4, 2007
    Try this:

    13 : WHERE customer_info.last_name = '#FORM.last_name#'

    Note the single quotes around #Form.LastName#

    Does this solve it?


    Also, you might want to insert things like wildcards into your search query to allow for "near hits". This will depend on your application, though.
    M
    margo_annAuthor
    Known Participant
    May 4, 2007
    Thank you using the single quotes worked. now I am able to get a list of records to choose from to update. However, I am having trouble with my update. I am not sure again about the WHERE clause and the how to pass the record ID. The error I receive when trying to update follows:

    Element LAST_NAME is undefined in URL.

    The error occurred in C:\Inetpub\wwwroot\spl_web_site\public_records\update.cfm: line 15

    13 : staff_fname='#form.staff_fname#',
    14 : staff_ph_ext='#form.staff_ph_ext#'
    15 : WHERE (customer_info.cust_id = #URL.last_name#)
    16 : </cfquery>
    17 : <body>

    The code of the update.cfm follows: Any help you can give is greatly appreciated as I am almost finished with this project.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices