0

I want to know the auto increment value before insert a row. That means didn't like that "Select MAX(id)". There may be deleted lines so I need auto increment value. How can I get it with query or c#?

  • 4
    this feels like you are going to implement a **race condition** .. You should let the database handle the auto increment.. Don't query the auto increment and insert yourself with that id . – Raymond Nijland Jul 27 '18 at 20:26
  • 1
    Why do you want to know the Id before inserting the row? What do you want to do with it? – Progman Jul 27 '18 at 20:28
  • 2
    I believe this question has your answer: [stack overflow](https://stackoverflow.com/questions/6761403/how-to-get-the-next-auto-increment-id-in-mysql) – Tim Jul 27 '18 at 20:31
  • @Progman I am will add a row in datagrid with datatable and same row will add database . I am will add on database with asynchronous. First add datagrid after than adding database with asynchronous. To add at first on datagrid I need last auto increment value for add id on datagrid. –  Jul 27 '18 at 20:35
  • @Tim I've tried LAST_INSERT_ID(). but it is return 0 everytime. –  Jul 27 '18 at 20:39
  • @kamilkunt You have to call LAST_INSERT_ID() after you run the INSERT query or use the API you are using to get the generated ID. – Progman Jul 27 '18 at 20:41

1 Answers1

0

I think you can use LAST_INSERT_ID() in your SQL Query and then you can increment +1 in your code the way you prefer... This way you'll have the next auto increment value.

Hope that this helps you! Let me know if this works...

Eduardo
  • 1
  • It is return 0 every time. –  Jul 27 '18 at 20:40
  • 1
    Oh sorry, it only works in other occasions, but I think that this code will work this time... Try this one: SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'tablename'; – Eduardo Jul 27 '18 at 21:02
  • And just to let you know, when you set a table as auto increment and you delete a row, lest suppose that the row had a auto increment value of 5, for example. And then after you deleted it, the next insert that you make will now have the auto increment value of 6 instead of using the value 5 again! Hope that this sorts you problem out... – Eduardo Jul 27 '18 at 21:07