Professional Web Applications Themes

2 sql statements in one <cfquery> - Coldfusion Database Access

hi. is it possible to do something like this? <cfquery datasource="chDev" name="test"> SELECT * FROM test WHERE id = #url.id# INSERT INTO test (blah) VALUES ('dlllele') </cfquery>...

  1. #1

    Default 2 sql statements in one <cfquery>

    hi.
    is it possible to do something like this?
    <cfquery datasource="chDev" name="test">
    SELECT *
    FROM test
    WHERE id = #url.id#
    INSERT INTO test (blah)
    VALUES ('dlllele')
    </cfquery>
    Kiriran Guest

  2. #2

    Default Re: 2 sql statements in one <cfquery>

    not possible..
    adonis1976 Guest

  3. #3

    Default Re: 2 sql statements in one <cfquery>

    Really? I do it all the time (MSSQL).

    Then again, is this behavior (prohibiting multiple SQL statements in CFQUERY)
    a "feature" of post-CF5 versions? I happen to think it's a DB-dependent
    behavior, but I don't have MX to test it.

    philh Guest

  4. #4

    Default Re: 2 sql statements in one <cfquery>

    I do believe that it is database dependent. I have seen it done in MSSQL, but I have never been able to do it with Oracle, via ODBC or native drivers, on either CF 4.5 or MX.

    Phil
    paross1 Guest

  5. #5

    Default Re: 2 sql statements in one <cfquery>

    coldfusion doesn't like it that way.. i have mx here and we have informix in the backend.. doesnt work with MX 7 atleast..
    adonis1976 Guest

  6. #6

    Default Re: 2 sql statements in one <cfquery>

    Most databases can handle multiple queries. <cfquery> can only pass multiple
    queries when the database driver allows it.

    SQL Server driver = yes
    MySQL driver = no
    Oracle driver = yes (IIRC)

    BUT you always seperate multiple queries with a semi-colon. This is the
    delimeter that tells the database one query ends.

    <cfquery datasource="chDev" name="test">
    SELECT *
    FROM test
    WHERE id = #url.id#
    ;
    INSERT INTO test (blah)
    VALUES ('dlllele')
    </cfquery>

    HTH

    cf_menace Guest

  7. #7

    Default Re: 2 sql statements in one <cfquery>

    I also want to put multiple queries on my page, but I want to show the old data
    and be able to update it to new. I keep getting an error though. I think its
    because the query is pulling the data and the other part of the form is
    changing the data. Do I need to have a second database for updates? or not show
    the old data when I need to update it? Thanks. NC

    shadowbambi50 Guest

  8. #8

    Default Re: 2 sql statements in one <cfquery>

    An example of what you are trying to do would be helpful. For instance, what does your SQL look like, and what error are you getting?

    Phil
    paross1 Guest

  9. #9

    Default Re: 2 sql statements in one <cfquery>

    Queries are at the top...
    <CFQUERY NAME="GetEmployeeInfo" DATASOURCE="EmployeeUpdate.mdb">
    SELECT *
    FROM Results
    Where Employee_ID = #URL.Employee_ID#
    </CFQUERY>
    Then a table with old data on the left and new data to imput on the right.
    <tr>
    <td>Employee Name </td>
    <td><CFOUTPUT QUERY="GetEmployeeInfo"><input name="Employee_Name"
    size="35" value="#GetEmployeeInfo.Employee_Name#"></cfoutput></td>
    <td width="177">New Name </td>
    <td width="373"><input name="new_name" size="35"></td>
    </tr>

    The error says:
    ODBC Error Code = 37000 (Syntax error or access violation)


    [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


    SQL = "UPDATE results SET date_submitted = '05/09/05,05/09/05', new name =
    'DELETE TESTER', department_branch = '*No Branch*,*No Branch*', department =
    'Customer Service_044000,*No Department*', position = 'call girl', supervisor =
    'carissa', temp = '1', voicemail8 = '0', notes3 = NULL, , notes4 = NULL, ,
    hr_submitting_form = '' where employee_ID = 816"

    Data Source = "EMPLOYEEUPDATE.MDB"


    The error occurred while processing an element with a general identifier of
    (CFQUERY), occupying doent position (2:1) to (2:64) in the template file
    d:\inetpub\wwwroot\changeofstatus\new_change_statu s\UpdateAction.cfm.


    shadowbambi50 Guest

  10. #10

    Default Re: 2 sql statements in one <cfquery>

    What is with the double commas after the NULLs?

    ....notes3 = NULL, , notes4 = NULL, , hr_submitting_form = ......

    Phil
    paross1 Guest

  11. #11

    Default Re: 2 sql statements in one <cfquery>

    Looks like there are more than one field with the same name. There are comma-delimited dates in the date update field, also.
    philh Guest

  12. #12

    Default Re: 2 sql statements in one <cfquery>

    Does this mean that I can't have the form pull info from a database and in the
    same form be able to change the data:
    Old info-(filled by query) New Info (update to same database)

    How can I have the old data displayed and yet be able to have the user change
    it if needed?

    shadowbambi50 Guest

  13. #13

    Default Re: 2 sql statements in one <cfquery>

    Yes, you can. You just need to associate the fields for each record with the
    primary key of the record, and handle the updates in the action page.

    For example, you have a field named "department_branch". The ID of the first
    record is, say, 4538. You would have to name the form field something like
    "department_branch_4538" to differentiate it from the other records.

    HTH,

    philh Guest

Similar Threads

  1. If Statements????
    By ccarterca in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: October 17th, 12:34 PM
  2. Max Pooled statements
    By syam in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: September 24th, 01:13 AM
  3. If statements in PHP?
    By barbedwire103 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 11th, 11:24 AM
  4. conditional sql statements
    By Bill in forum ASP
    Replies: 2
    Last Post: August 29th, 07:51 PM
  5. If/else statements - help.
    By Chris in forum PHP Development
    Replies: 1
    Last Post: June 28th, 01:55 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