[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-win32
Subject:    Re: getting ID back after an insert statement
From:       "Fredrick Bartlett" <palmtreefrb () earthlink ! net>
Date:       2005-08-23 16:13:48
Message-ID: 003e01c5a7fd$a5d0b490$3b526e45 () REGITLAP
[Download RAW message or body]

Yes, but for some reason doing it that way returned 0 records for me. That
is why I did the WHERE ID = last_insert_id()

----- Original Message ----- 
From: <SGreen@unimin.com>
To: "Fredrick Bartlett" <palmtreefrb@earthlink.net>
Cc: "Mark Mchugh" <mark_mch@yahoo.com>; "mysql list" <win32@lists.mysql.com>
Sent: Monday, August 22, 2005 12:50 PM
Subject: Re: getting ID back after an insert statement


> I think you are putting a read lock on `contacts` when you don't need to.
> Try this instead:
>
> var lastId;
> var SQL = "SELECT last_insert_id()";
> var rs = connMySQL.Execute(SQL)
> if(rs.Eof==false){
>  lastId = rs(0);
> }
>
> That way you only hit the connection and you don't add a lock to any
> tables. The LAST_INSERT_ID() is a value cached on the server on a
> per-connection basis so you shouldn't need to query any tables to get to
> it.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> "Fredrick Bartlett" <palmtreefrb@earthlink.net> wrote on 08/22/2005
> 02:13:53 PM:
>
> > This is what I had to do...
> > var lastId;
> > var SQL = "SELECT ID FROM contacts WHERE ID = last_insert_id()";
> > var rs = connMySQL.Execute(SQL)
> > if(rs.Eof==false){
> >  lastId = rs("ID");
> > }
> >
> > ----- Original Message ----- 
> > From: "Mark Mchugh" <mark_mch@yahoo.com>
> > To: "mysql list" <win32@lists.mysql.com>
> > Sent: Monday, August 22, 2005 7:21 AM
> > Subject: getting ID back after an insert statement
> >
> >
> > > hi,
> > > I am using the following code
> > >
> > > Set rs = New ADODB.Recordset
> > > rs.ActiveConnection = connMySQL
> > > rs.Open sqlstr
> > >     sqlstr = "insert into
> > > contacts(classification,companyname,fname, lname,
> > > addr1,
> > > addr2,addr3,town,county,position,landline,fax,email,mobile)
> > > values ( " & "'" & ctype & " ','" & Text13.Text &
> > > "','" & Text1.Text & "','" & Text2.Text & "', '" &
> > > Text3.Text & "','" & Text4.Text & "', '" & Text5.Text
> > > & "','" & Text6.Text & "','" & Text7.Text & "','" &
> > > Text9.Text & "','" & Text10.Text & "','" & Text11.Text
> > > & "','" & Text12.Text & "','" & Text13.Text & "');"
> > >
> > >
> > > this table has an ID field which is auto-incremented.
> > > is there a way to get the ID of the item added from
> > > the above SQL without having to re-query the database?
> > >
> > >
> > >
> > > thanks
> > >
> > > MArk
> > >
> > >
> > >
> > > ____________________________________________________
> > > Start your day with Yahoo! - make it your home page
> > > http://www.yahoo.com/r/hs
> > >
> > >
> > > -- 
> > > MySQL Windows Mailing List
> > > For list archives: http://lists.mysql.com/win32
> > > To unsubscribe:
> > http://lists.mysql.com/win32?unsub=palmtreeFRB@earthlink.net
> > >
> >
> >
> > -- 
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:    http://lists.mysql.com/win32?unsub=sgreen@unimin.com
> >
>


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/win32?unsub=mysql-win32@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]