Insert then Query

Report · Mar 06, 2007

I have a small CFM application on our server. It worked well for several years. However, we just upgraded the coldfusion server and one item is not working.

I used to have folks fill out a form. The data was inserted into the database. I would query the database on the same page in which I did the insertion and all was fine....every record was available.

Now, however, when I query the database after the insertion, all records up to the lastest insertion are reflected. In other words, the page insertion data is not reflected in the query. However, the data is successfully inserted. (Inspection with other methods indicates the data was successfully inserted.)

Here is a scaled down code snippet.

<cfinsert datasource=#WList_DSN# tablename="Wait"
formFields="Fname,
Lname,
Snumber,
Course,
Password,
Phone,
Email">
<cfquery name="get_position" datasource=#WList_DSN# username=#WList_USERNAME# password=#WList_PASSWORD#>
SELECT ID, Fname, Lname, Snumber, Phone, Email FROM Wait
WHERE Course = '#form.Course#'
ORDER by ID
</cfquery>
<html>
<head>
<title>Test Page</title>
<body>
<cfoutput query="get_data">
#FNAME# #LNANE#<br>
</cfoutput>
</body>
</html>

#FNAME# and #LNAME# in the output show each record in the database except for the one just inserted. Is there a way to remedy this? I want to conduct the query on the same page as the insertion, if at all possible.

Any help would be greatly appreciated.

Thanks...

Report · Mar 07, 2007

The output of query "get_data" is probably the query BEFORE the insert. The one you show running after the insert is "get_position", therefore:

<cfoutput query="get_position">
#FNAME# #LNAME#<br>
</cfoutput>

- Mike

Report · Mar 08, 2007

Sorry the "data" versus "position" was a translation problem when I was paring down the code for presentation.

What I found was the database takes almost 5 seconds to update versus near instantaneous before the software upgrade. I placed a pause betweent the insert and the query and the code performs as before.

Report · Mar 08, 2007

You should find out why it's suddenly taking 5 seconds to insert a record. Maybe you are no longer using the most appropriate driver.

Report · Mar 09, 2007

You might try changing from cfinsert to an actual query with an insert statement (Dan, who posted above, had suggested that and it "smoothed" alot of my forms right out).

- Mike

Adobe Community

Insert then Query