none
Auto Generate ID

    Question

  • Hi There,

    I am trying to auto Generate ID in a textbox, not sure where i am doing wrong but i am getting an error message as below for the posted code

    Error : Connection must be Valid and Open

           Try
                If AptusCnn.State = 1 Then
                    AptusCnn.Close()
                End If
                Dim Number As Integer
                Dim cmd As New MySqlCommand
                AptusCnn.Open()
                cmd.CommandText = "SELECT MAX(Svc_Cert_ID) FROM cp_servicability "
                If IsDBNull(cmd.ExecuteScalar) Then
                    Number = 1
                    txtSvcCertID.Text = Number
                Else
                    Number = cmd.ExecuteScalar + 1
                    txtSvcCertID.Text = Number
                End If
                cmd.Dispose()
                AptusCnn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try

    Although i have opened the connection ( Aptuscnn.open ) i am still getting the error, what could be wrong ?

    Thank You

    Learning Bee !!


    Dotnet.Explorer

    Wednesday, June 05, 2013 9:06 PM

Answers

  • Hi,

    the main reason is, that you don't assign the commands connection property.

    But the code has some more problems.
    As database queries are time consuming you should avoid multiple ExecuteScalar calls.
    The NULL case can be handled using the MySQL IFNULL function, see example below.

    Be aware that SELECT MAX() isn't save in a multi user environment.
    Consider using an AUTO_INCREMENT column for the key as Cor already suggested.

    If you are using a single connection the method shouldn't change the connection state on exit.
    A alternative solution would be using local connections and let the connection pooling manage the connections.

    A possible implementation as a function:

        Function GetNextSvcCertID() As Integer
            Dim initialState = AptusCnn.State
            Try
                ' Open Connection if not already open
                If AptusCnn.State <> ConnectionState.Open Then
                    AptusCnn.Open()
                End If
    
                ' Assign SQL and Connection
                ' Uses IFNULL to assign default if null (no entry)
                Dim cmd As New MySqlCommand(
                    "SELECT IFNULL(MAX(Svc_Cert_ID) + 1, 1) FROM cp_servicability;",
                    AptusCnn)
    
                Return CInt(cmd.ExecuteScalar())
            Catch ex As Exception
                ' Some logging would be better
                Console.WriteLine("GetNextSvcCertID: " & ex.ToString())
    ' rethrow has to be handled by caller Throw Finally ' Close Connection if initially closed If initialState <> ConnectionState.Open Then AptusCnn.Close() End If End Try End Function
    Regards, Elmar

    • Edited by Elmar BoyeMVP Thursday, June 06, 2013 7:52 AM
    • Marked as answer by Nike_Nick Thursday, June 06, 2013 3:50 PM
    Thursday, June 06, 2013 7:51 AM

All replies

  • Probably you have set that in your database an AutoIdentifier. That one is created by and inside the database itself.

    If you want to create an unique identifier yourself, then use a GUID instead of an integer.

    Don't try to use ID like this for external use. These are for the database to make data unique, not for end users.


    Success
    Cor




    Wednesday, June 05, 2013 9:23 PM
  • Have you stepped through your code in the debugger to see exactly where control jumps to the Catch block?

    Try to identify that point to see the failing statement.

    Post back here which statement actually throws the exception.

    Wednesday, June 05, 2013 9:31 PM
  • Hi,

    the main reason is, that you don't assign the commands connection property.

    But the code has some more problems.
    As database queries are time consuming you should avoid multiple ExecuteScalar calls.
    The NULL case can be handled using the MySQL IFNULL function, see example below.

    Be aware that SELECT MAX() isn't save in a multi user environment.
    Consider using an AUTO_INCREMENT column for the key as Cor already suggested.

    If you are using a single connection the method shouldn't change the connection state on exit.
    A alternative solution would be using local connections and let the connection pooling manage the connections.

    A possible implementation as a function:

        Function GetNextSvcCertID() As Integer
            Dim initialState = AptusCnn.State
            Try
                ' Open Connection if not already open
                If AptusCnn.State <> ConnectionState.Open Then
                    AptusCnn.Open()
                End If
    
                ' Assign SQL and Connection
                ' Uses IFNULL to assign default if null (no entry)
                Dim cmd As New MySqlCommand(
                    "SELECT IFNULL(MAX(Svc_Cert_ID) + 1, 1) FROM cp_servicability;",
                    AptusCnn)
    
                Return CInt(cmd.ExecuteScalar())
            Catch ex As Exception
                ' Some logging would be better
                Console.WriteLine("GetNextSvcCertID: " & ex.ToString())
    ' rethrow has to be handled by caller Throw Finally ' Close Connection if initially closed If initialState <> ConnectionState.Open Then AptusCnn.Close() End If End Try End Function
    Regards, Elmar

    • Edited by Elmar BoyeMVP Thursday, June 06, 2013 7:52 AM
    • Marked as answer by Nike_Nick Thursday, June 06, 2013 3:50 PM
    Thursday, June 06, 2013 7:51 AM
  • Thank You Elmar !! That helped !!

    Dotnet.Explorer

    Thursday, June 06, 2013 3:50 PM