2 sql statements in one <cfquery>

[Kiriran]

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>

[adonis1976]

not possible..

[philh]

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.

[paross1]

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

[adonis1976]

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

[cf_menace]

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

[shadowbambi50]

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

[paross1]

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

[shadowbambi50]

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 document position (2:1) to (2:64) in the template file
d:\inetpub\wwwroot\changeofstatus\new_change_statu s\UpdateAction.cfm.

[paross1]

What is with the double commas after the NULLs?

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

Phil

[philh]

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

[shadowbambi50]

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?

[philh]

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,