How to get data from newly added row in mysql database using VB6.0

How to get data from newly added row in mysql database using VB6.0

am 16.09.2005 13:09:08 von Shepherd Madziwa

------_=_NextPart_001_01C5BAAF.0F14ED40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I have this nagging problem with my VB6.0 application. I am creating a =
new record and I want to carry the record ID for future references down =
the code. This record ID is AUTO_INCREMENT and I am using MYSQL =
database. My code is as follows after the definition of my recordset and =
initialising it:
rs.addnew
rs!lastname =3D text1.text
rs!firstname=3Dtext2.text
rs.update
varRecID =3D rs!ClientID
rs.close

Now when I trace the program "varRecID" is zero(0), but if I browse the =
table in MYSQL I can see the record ID.

Where am I going wrong.

Thanks.


------_=_NextPart_001_01C5BAAF.0F14ED40--

RE: How to get data from newly added row in mysql database using VB6.0

am 16.09.2005 17:57:28 von Ethan Moe

Try this:

varRecID =3D rs!ClientID
rs.update

MySQL assigns an autonumber id as soon as the new record created, even
though it may not be displayed and it's not officially the correct id
until the record is saved. Once you update, the record is no longer
active, so you can't see it without requerying. I ran into the same
problem with an Access application. It seems counter-intuitive to get
the id before the record is saved, but it does work.

Ethan Moe


-----Original Message-----
From: Shepherd Madziwa [mailto:SMadziwa@psmi.co.zw]=20
Sent: Friday, September 16, 2005 4:09 AM
To: MYODBC@LISTS.MYSQL.COM
Subject: How to get data from newly added row in mysql database using
VB6.0
Importance: High

I have this nagging problem with my VB6.0 application. I am creating a
new record and I want to carry the record ID for future references down
the code. This record ID is AUTO_INCREMENT and I am using MYSQL
database. My code is as follows after the definition of my recordset and
initialising it:
rs.addnew
rs!lastname =3D text1.text
rs!firstname=3Dtext2.text
rs.update
varRecID =3D rs!ClientID
rs.close

Now when I trace the program "varRecID" is zero(0), but if I browse the
table in MYSQL I can see the record ID.

Where am I going wrong.

Thanks.




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: How to get data from newly added row in mysql database using VB6.0

am 19.09.2005 01:11:11 von jbonnett

The approach I use would go like this.

varRecId =3D GetLastInsertID

where GetLastInsertID looks like this

Public Function () As Long
Dim rs As MYSQL_RS
=20
Set rs =3D RunSQL("SELECT LAST_INSERT_ID() AS ID")
GetLastInsertID =3D CLng(rs.Fields(0).Value)
rs.CloseRecordset
End Function

Depending on how you are connecting to MySQL you may need to adjust this
routine. I am using VBMySQLDirect. If you are using ADO then the routine
would go like this.

Public Function () As Long
Dim rs As ADODB.Recordset
=20
Set rs =3D gConn.Execute("SELECT LAST_INSERT_ID() AS ID")
GetLastInsertID =3D CLng(rs.Fields(0).Value)
rs.Close
End Function

gConn is the current connection object you are using.

John B.

-----Original Message-----
From: Shepherd Madziwa [mailto:SMadziwa@psmi.co.zw]=20
Sent: Friday, 16 September 2005 8:39 PM
To: MYODBC@LISTS.MYSQL.COM
Subject: How to get data from newly added row in mysql database using
VB6.0

I have this nagging problem with my VB6.0 application. I am creating a
new record and I want to carry the record ID for future references down
the code. This record ID is AUTO_INCREMENT and I am using MYSQL
database. My code is as follows after the definition of my recordset and
initialising it:
rs.addnew
rs!lastname =3D text1.text
rs!firstname=3Dtext2.text
rs.update
varRecID =3D rs!ClientID
rs.close

Now when I trace the program "varRecID" is zero(0), but if I browse the
table in MYSQL I can see the record ID.

Where am I going wrong.

Thanks.


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg