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=gcdmw-win32@m.gmane.org

Re: getting ID back after an insert statement

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"
To: "mysql list"
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=gcdmw-win32@m.gmane.org

Re: getting ID back after an insert statement

--=_alternative 006D592485257065_=
Content-Type: text/plain; charset="US-ASCII"

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" 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"
> To: "mysql list"
> 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
>

--=_alternative 006D592485257065_=--

Re: getting ID back after an insert statement

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:
To: "Fredrick Bartlett"
Cc: "Mark Mchugh" ; "mysql list"
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" 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"
> > To: "mysql list"
> > 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=gcdmw-win32@m.gmane.org

RE : getting ID back after an insert statement

------=_NextPart_000_0000_01C5DD3B.02AF0280
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Dim NewID as Long

Dim cn as New ADODB.Connection

Dim rs as New ADODB.Recordset

Dim QueryString as string

..

..

Set up your ADODB Connection object

rs.ActiveConnection = cn

rs.CursorType = adOpenStatic

rs.CursorLocation = adUseClient



QueryString = "INSERT INTO .."

..

..



cn.Execute QueryString

rs.Open "SELECT LAST_INSERT_ID()"



If rs.AbsolutePosition <> -1 Then

NewID = rs(0)

End If



Fredrick Bartlett wrote:

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()



The key is to use a client side cursor for the recordset . with
rs.CursorLocation = adUseServer the Query will return 0.

I think adUseServer is the default so you have to set it to adUseClient in
your code.



Regards







------=_NextPart_000_0000_01C5DD3B.02AF0280--