none
get maximum number and plus 1 RRS feed

  • Question

  • what i want to do is to get the maximum Id from mysql and add 1 to make it auto increment without setting the Field to auto Increment
    Monday, October 22, 2018 12:01 PM

Answers

  • here is my query

    "select * from diagnosis where record_id = (SELECT MAX(record_id) FROM diagnosis)"

    i simply want to get the max record_id and plus 1

    Hi,

    No need to select *

    to

    Select max(record_id) From diagnosis

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by no[one] Saturday, November 24, 2018 7:15 AM
    Monday, October 29, 2018 7:53 AM

All replies

  • Hi

    If you choose to manage the ID column yourself, then you will also need to manage the ID's when rows are moved/added/removed. Here is a Sub I use in one application which is called from various places and works fine (I haven't checked impact on a large set of data. This code deals exclusively with a DataGridView (called DGV in this example), and uses the RowHeader cells to hold the ID numbers.

    Image

    Example Sub code

      Sub Renumber()
        Dim num As Integer = 1
        Dim textSize As New Size(0, 0)
        Dim g As Graphics = DGV.CreateGraphics
        For Each r As DataGridViewRow In DGV.Rows
          textSize = TextRenderer.MeasureText(g, num.ToString, DGV.DefaultCellStyle.Font, textSize, TextFormatFlags.NoPadding)
          r.HeaderCell.Value = num.ToString
          num += 1
        Next
        DGV.RowHeadersWidth = 38 + textSize.Width
      End Sub


    Regards Les, Livingston, Scotland


    • Edited by leshay Monday, October 22, 2018 1:00 PM
    Monday, October 22, 2018 12:59 PM
  • You should always use auto-incrementing for a primary key column but when you are sure there are no conflicts get the next id from the database table rather than the DataGridView. Should look something lile this.

    SELECT id +1 AS NextId  FROM YourTableName ORDER BY id DESC LIMIT 1
    Using a connection and a command object use ExecuteScalar cast to a Integer to get the value of id +1.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, October 22, 2018 1:35 PM
    Moderator
  • Hi,

    You can use the following code to plus 1 to the largest Id in the database.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
    
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Using conn = New SqlConnection(constr)
                conn.Open()
                cmd = New SqlCommand("update student set id =(Select max(id) From Student)+1 where id=(Select max(id) From Student)", conn)
                cmd.ExecuteNonQuery()
            End Using
    
        End Sub
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 23, 2018 8:02 AM
  • I agree with Karen in that I would let my DBM handle the identity/AI, however if this is not an option I would suggest that you commit the row to the table immediately. This will reduce your chance of a collision by 99.3%

    For example:

        Private Sub ButtonGetID_Click(sender As Object, e As EventArgs) Handles ButtonGetID.Click
            Dim myNewID As Integer = GetNewID()
            Using MysqlConn As New MySqlConnection("Server=***;uid=***;pwd=***;database=sandbox;sslmode=none")
                MysqlConn.Open()
                Using Cmd As New MySqlCommand("INSERT INTO autoinc SET id=@NewID,col1=@Col1", MysqlConn)
                    Cmd.Parameters.AddWithValue("@NewID", myNewID)
                    Cmd.Parameters.AddWithValue("@Col1", "Col1 Value")
                    Cmd.ExecuteNonQuery()
                End Using
                MysqlConn.Close()
            End Using
            
        End Sub
    
        Private Function GetNewID() As Integer
            Using MysqlConn As New MySqlConnection("Server=***;uid=***;pwd=***;database=sandbox;sslmode=none")
                MysqlConn.Open()
                Using Cmd As New MySqlCommand("SELECT ID FROM autoinc ORDER BY ID Desc LIMIT 1", MysqlConn)
                    Dim NewID As Object = Cmd.ExecuteScalar()
                    If NewID IsNot Nothing Then
                        Return CInt(NewID) + 1
                    Else
                        Return 1
                    End If
                End Using
                MysqlConn.Close()
            End Using
        End Function


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, October 24, 2018 2:52 AM
  • here is my query

    "select * from diagnosis where record_id = (SELECT MAX(record_id) FROM diagnosis)"

    i simply want to get the max record_id and plus 1

    Sunday, October 28, 2018 12:36 AM
  • here is my query

    "select * from diagnosis where record_id = (SELECT MAX(record_id) FROM diagnosis)"

    i simply want to get the max record_id and plus 1

    Hi,

    No need to select *

    to

    Select max(record_id) From diagnosis

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by no[one] Saturday, November 24, 2018 7:15 AM
    Monday, October 29, 2018 7:53 AM