asked on

Getting the last record insert

Hi,

So i am doing a copy fuction.. and I insert the record and using a ID from the session.id...

So after the insert statement i run a select statemetn with the ID and can return all the records from that ID.. but i want to get the last entry by that ID in the select statement... I am not sure how to do that??

Thx,

Rich

schubach

I'm not sure if I understand your question. If you want to select the last ID inserted into any table, you would run

select last_insert_id()

Open in new window

ASKER CERTIFIED SOLUTION

Coast Line

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Coast Line

also, if above is the key to your question, you seriously need to elaborate what you are doing

Coast Line

oops! Statement Corrected

also, if above is not the key to your question, you seriously need to elaborate what you are doing

cookmyster

ASKER

I am getting this error...

Element GENERATED_KEY is undefined in LASTID.

Coast Line

can u show me how you are doing it so i can check

cookmyster

ASKER

<cfset today = #dateformat(now(), "mm/dd/yy")#>
      <cfquery result="lastID" name="jobinsert" datasource="#Application.dbname#" username="#Application.username#" password="#application.password#" dbtype="ODBC">
            insert into jobs (company_id, job_title, job_location, job_description, job_qualifications, job_otherinfo, job_salary, job_date, job_contactname, job_email)
                  values ('#session.cid#', '#form.txtjobtitle#', '#jobinfo.job_location#', '#jobinfo.job_description#', '#jobinfo.job_qualifications#', '#jobinfo.job_otherinfo#', '#jobinfo.job_salary#', now(), '#jobinfo.job_contactname#', '#jobinfo.job_email#')
      </cfquery>

<cfset lastDI = lastID.GENERATED_KEY>
<cfoutput>#lastDI#</cfoutput>

Coast Line

are u trying like this

<cfquery name="addData" datasource="#DSN#" result="addDataResult">
                                        INSERT INTO tbldata(Path,Pathname)
                                        VALUES
                                (<cfqueryparam value="#path#" cfsqltype="CF_SQL_VARCHAR">,
                                <cfqueryparam value="#pathname#" cfsqltype="CF_SQL_VARCHAR">)
                                </cfquery>
        <cfset variables.newID = addDataResult.GENERATED_KEY>

Open in new window

Coast Line

why are u using " dbtype="ODBC", its no needed

Check the Code i gave it

Coast Line

i forgot to ask, are you using Coldfusion 8 and above, if yes, then the code i gave u will work

cookmyster

ASKER

I am using coldfusion 9.. and i took the ODBC off... still kicking the error....

gdemaria

(no points)

The variable is for MySQL if you're using a different database, please let us know

<cfset today = #dateformat(now(), "mm/dd/yy")#>
<cfquery result="lastID" name="jobinsert" datasource="#Application.dbname#" username="#Application.username#" password="#application.password#" dbtype="ODBC">
insert into jobs (company_id, job_title, job_location, job_description, job_qualifications, job_otherinfo, job_salary, job_date, job_contactname, job_email)
values ('#session.cid#', '#form.txtjobtitle#', '#jobinfo.job_location#', '#jobinfo.job_description#', '#jobinfo.job_qualifications#', '#jobinfo.job_otherinfo#', '#jobinfo.job_salary#', now(), '#jobinfo.job_contactname#', '#jobinfo.job_email#')
</cfquery>

You can dump the variable...

<cfdump var="#lastID#">

Coast Line

do this thing, runa simple test

<cfquery datasource="test" result="result">
insert into test(name)
values('myselfrandhawa')
</cfquery>

<cfdump var="#result#">

make sure test table exists and change the dsn name

_agx_

(no points)

> I am getting this error...
> Element GENERATED_KEY is undefined in LASTID.

Silly question, but your table does have an AUTOINCREMENT column correct?

cookmyster

ASKER

Hey Guys... figured the problem out... i add it pointing to an access database instead of my mysql database. So they original solutions did work...

thank you for all your help