i have a MySQL table with the following columns: employeeID, firstName, lastName, gender, salary. whenever i insert a new record for this table i want to be able to retrieve the employeeID of that record. how should i write the commands in VB.Net

Recommended Answers

All 2 Replies

I do not know much about MySQL but if you have the aggregate function MAX() you can use it to retrieve the last inserted record in a table as

SELECT MAX(employeeID) FROM Table 

or you can you use a subquery as

SELECT firstName, lastName, gender, salary ... FROM Table WHERE employeeID IN/= (SELECT MAX(employeeID) FROM Table) 

this will return you the data of an employee which has the max employeeID value number for example if you have 5 employees numbered as 1, 2, 3, 4 and 5 you will get the data for the employee number 5. Hope this work for you
if you do not have the MAX() function in MySQL try to google for its MySQL equivalent. You can also use the COUNT() aggreagate function if you have it or google for its equivalent as it will return you a the number of records you have as on the above example if you have 5 reords it will return you a value 5 so the result will look like it is

SELECT firstName, lastName, gender, salary ... FROM Table WHERE employeeID = 5

The differenece between MAX() and COUNT() is that MAX() will return you the maximum value inside the table not the number of records meaning that if you have unordered employeeID number say 1, 12, 25, 40 and 60 so it will return you 60 as a result, however, the COUNT() function will return you 5 as the they are 5 numbers means 5 records. I hope you got the idea and I explain it well for you

Use the top 1 function and then order by ID desc for you to get the last ID.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.