How to make auto id to function in module?

Question

i have this code

 With ServiceInformationDialog


        Dim cmd As New SqlCommand
        Dim id As Integer
        cn.Open()
        cmd.Connection = cn
        cmd.CommandText = " SELECT MAX(Service_ID) FROM Table_Service"

        If IsDBNull(cmd.ExecuteScalar) Then

            id = 1
            .ServiceIDserviceTextBox1.Text = id
        Else
            id = cmd.ExecuteScalar + 1
            .ServiceIDserviceTextBox1.Text = id

        End If
        cmd.Dispose()
    End With

    cn.Close()

it work but i want to make it as a function, so i can use it all over my project, and i want to know how to make it ?

Dave Doknjas · Answer 1 · 2014-04-26T20:07:52.273

1

Modules in VB are typically used for utility functions such as this - I don't know where 'cn' resides, so you'll have to figure that out, in addition to naming the module something more meaningful for your application:

Friend Module Utility
    Public Function MaxServiceID(ByVal column As String, ByVal table As String) As Integer
        Dim cmd As New SqlCommand
        cn.Open()
        cmd.Connection = cn
        cmd.CommandText = " SELECT MAX(" & column & ") FROM " & table

        Dim id As Integer
        If IsDBNull(cmd.ExecuteScalar) Then
            id = 1
        Else
            id = cmd.ExecuteScalar + 1
        End If

        cmd.Dispose()
        cn.Close()

        Return id
    End Function
End Module

Then you would have:

ServiceInformationDialog.ServiceIDserviceTextBox1.Text = Utility.MaxServiceID("Service_ID", "Table_Service")

edited Apr 26 '14 at 20:07

answered Apr 26 '14 at 19:11

Dave Doknjas

6,394
1
15
28

can i set parameter for colunms and table, if i can do it i thank i can u it in any forms . – user3198588 Apr 26 '14 at 19:14
Sure you can. Give it a try. – Dave Doknjas Apr 26 '14 at 19:17
I've already tried in many ways, but still couldn't do it can u give me an example ? – user3198588 Apr 26 '14 at 19:30
See the link provided by Plutonix. – Dave Doknjas Apr 26 '14 at 19:55
i see but i don't want to use tool to do it. – user3198588 Apr 26 '14 at 20:01
I've edited my answer to include parameters - if you have trouble with adding parameters to a method then you should be reviewing the basics before you get in over your head. Also, for security reasons you probably don't want to be constructing SQL query strings in your code. – Dave Doknjas Apr 26 '14 at 20:09

score 0 · Answer 2 · edited May 23 '17 at 12:28

When you retrieve an ID like that, other user may have already inserted a record with such an ID, so you will get a unique key violation error, when trying to insert your record, due to concurrency issue.

A good way to do this, is to let DB handle your unique ID, for MSSQL, usually an IDENTITY column is used, for Oracle 10/11g you can use triggers, and 12c supports this feature out of the box. Then retrieve an ID of the already inserted record (if you need it for some reason). For SQL, see:

Best way to get identity of inserted row?

How to make auto id to function in module?

2 Answers2