Document Upload

[danarashad]

I am having some trouble, I am uploading an image. Then I do an insert into a DB, and do a select to grab the last insert. But for some reason I'll get an error. I don't get the error all of the time, but I do get it enough where its becoming a problem. I am using cflock, because I was told that would make sure that the insert happened before I do my select statement.

Here's the code I am using.

<cflock timeout="10">
<cfinsert tablename="gallery" datasource="table" formfields="name, position, image1">
<cfquery name="getNewID" datasource="table">
SELECT MAX(rec_id) as newID FROM gallery
</cfquery>

<cfquery name="getimage" datasource="table">
select image1 from gallery where rec_id=#getnewID.newID#
</cfquery>
</cflock>

<cfoutput>(#getimage.image1#)</cfoutput><br />

 

[cfSearching]

What is the error?

I do not think that will guarantee you get the correct record id. There are better ways to do that. What database are you using?

----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

Here's the error message I get.
Error Diagnostic Information
An error occurred while evaluating the expression:
#getimage.image1#
Error resolving parameter GETIMAGE.IMAGE1
ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:
I am using microsoft access. What is a better way. I was told this was a better way.
Thanks for your help.

 

[cfSearching]

Curious. What version of CF and Access are you using?

As an aside, the code should have error handling anyway. Just in case the record cannot be inserted or retrieved for some unexpected reason.

1) If rec_id is an autonumber column, I believe you could use @@identity to retrieve the new id value. Just remove the cflock and enclose both the insert and select inside a cftransaction. Totally untested.

<cftransaction>
    <cfinsert ...>

    <cfquery name="getNewID" datasource="table">
       SELECT @@IDENTITY as NewID
    </cfquery>
</cftransaction>

... rest of code

2) You could use a UUID to retrieve the record id. Then you would not need transactions or locking. See here for a good description

http://mysecretbase.com/get_the_last_id.cfm

----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

I am using cf 5, I am going to upgrade to 8, but at this time I can not. I think using an older version might be one of the problems. I am using microsoft access 2003. I"ll give this a shot.

 

[cfSearching]

I suspect it is more how the queries are structured. One of the methods mentioned should work better.

Btw, I only asked about version because the error message was a slightly different than what I would expect for MX7 or CF8.



----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

I've tried
<cftransaction>
<cfinsert ...>
<cfquery name="getNewID" datasource="table">
SELECT @@IDENTITY as NewID
</cfquery>
</cftransaction>
And it works for about 6 inserts then it breaks? I'll give the UUID a try.

 

[danarashad]

No worries about the version thing, I am using your brain for help, and if knowing what version I have will help, then by all means ask. We are going to switch to 8, but as of now, I have tons of users asking why, does this keeping happening. So I am trying to appease the masses.

 

[cfSearching]

What do you mean breaks? Same error as before?

I do not really use cfinsert. So I do not know if using cfinsert, as opposed to a regular insert, makes any difference.

The UUID method is probably the most bullet proof. But you may have to modify your insert.

----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

So I am trying to appease the masses

Yes. Because they frequently turn into angry mobs

----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

By break I mean I get the error.
An error occurred while evaluating the expression:
#getimage.image1#

I've tried all of the examples you gave. And after I do the 5th insert. I get the error. I've stopped using the cfinsert and went to using a sql insert ie: INSERT INTO mytable. and i still get the error.
I think the problem is with inserting. For whatever reason it will not insert, sometimes. How to overcome that, I am not really sure.

 

[danarashad]

Thanks for all of your help.

 

[cfSearching]

Inserting should not cause a problem. Though Access is not really a production database.

Are the inserts inside a loop, or are you just running a series of tests? Also What is the value of #getnewID.newID# when the error occurs?


----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

No I am not inserting inside a loop.

The value of #getnewID.newID# is nothing. What I am doing after I insert I am querying the db, to get the last ID. I am querying using your UUID statement. Its not inserting into the database. Keep in mind it does work, but after the 5th insert it breaks, giving me this error.
An error occurred while evaluating the expression:
#getimage.image1#
Error near line 77, column 13.
I am trying to use cftry and cfcatch. But I don't know how to use it.

 

[cfSearching]

The value of #getnewID.newID# is nothing

That is what I suspected, but that should cause the getimage query to fail right here:

where rec_id=#getnewID.newID#

So the code should halt and never make it to the cfoutput statement. 1) Did you remove the cflock and 2) can you post the code you are using?



----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

Thats what I thought also. But it keeps going as if it got inserted.
Ok here's the code I am using.

<cfset variables.newUUID=createUUID()>
<cfquery datasource="#application.myDSN#">
INSERT INTO gallery (name,image1,UUID)
VALUES ('#form.Name#','#form.image1#','#variables.newUUID#')
</cfquery>
<cfquery name="getnewID" datasource="#datasource#">
SELECT id FROM gallery WHERE UUID='#variables.newUUID#'
</cfquery>
<p>
<cfoutput>#getnewID.id#</cfoutput>
</p>
ID is a autonumber field. It errors on the cfoutput

 

[danarashad]

Shouldn't this catch the error. if it doesnt insert shouldn't this catch send me to espn.
<cftry>
<cfset variables.newUUID=createUUID()>
<cfquery datasource="#application.myDSN#">
INSERT INTO gallery (name,image1,UUID)
VALUES ('#form.Name#','#form.image1#','#variables.newUUID#')
</cfquery>
<cfquery name="getimage" datasource="#datasource#">
select image1 from gallery where rec_id= <cfqueryparam cfsqltype="cf_sql_integer" value="#getnewID.newID#">
</cfquery>
<p>
<cfoutput>#getnewID.id#</cfoutput>
</p>
<cfcatch> <cflocation url="

http://www.espn.com">

</cfcatch>
</cftry>

 

[cfSearching]

The last code example does send me to espn.com, because the "getnewID" query does not exist. I assume you did that deliberately.

But bear in mind there is a difference between an error the query returning zero records. That should not happen, but if it did that would not necessarily throw an error. Well .. maybe it does under CF5. I am not certain. It does not under later versions.

What happens if you try this code (not tested)

<cftry>
   <cfset variables.newUUID=createUUID()>
   <cfquery datasource="#application.myDSN#">
      INSERT INTO gallery (name,image1,UUID)
      VALUES ('#form.Name#','#form.image1#','#variables.newUUID#')
   </cfquery>

   <cfquery name="getimage" datasource="#datasource#">
      select 	rec_id, image1 
      from 	gallery 
      where 	UUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.newUUID#">
   </cfquery>
   <p>
      <cfoutput>
      new id = #getimage.rec_id#<br>
      image1 = #getimage.image1#<br>
      </cfoutput>
   </p>
   <cfcatch> <cflocation url="[URL unfurl="true"]http://www.espn.com">[/URL] </cfcatch>
</cftry>

----------------------------------

http://cfsearching.blogspot.com/

 

[danarashad]

Thanks I got that to work.