MySQL ODBC Connector - How to get data from newly added row in mysql database using VB6.0

Author

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

Shepherd Madziwa

2005-09-16, 7:23 am

------ _=_NextPart_001_01C5
BAAF.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_01C5
BAAF.0F14ED40--

Ethan Moe

2005-09-16, 11:23 am

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 & #91;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? unsu...sie.nctu.edu.tw

2005-09-18, 8:23 pm

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 & #91;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? unsu...sie.nctu.edu.tw

Sponsored Links