时间戳

首页 > 解决方案 > how to generate custom id using year and integer in vb.net

vb.net - how to generate custom id using year and integer in vb.net

问题描述

I want to make a custom ID Number using year and integer. This is my code:

    Dim idnumber As Integer
    Dim yrVal As String = DateTime.Now.Year
    idnumber = yrVal + "0001"

    textboxIDNumber.Text = idnumber

    Connection()
    sql = "SELECT * FROM Info WHERE idnumber LIKE '" & textboxIDNumber.Text & "'"
    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) 
    If rs.Fields(0).Value <> idnumber Then 
        rs.AddNew()
        rs.Fields(0).Value = textboxIDNumber.Text
        rs.Fields(1).Value = textboxLastName.Text
        rs.Fields(2).Value = textboxFirstName.Text
        rs.Fields(3).Value = textboxMiddleName.Text
        rs.Update()
        MsgBox("Successfully Added!", MsgBoxStyle.Information, "Message")
    Else
        If rs.Fields(0).Value = idnumber Then
            rs.AddNew()
            textboxIDNumber.Text += 1
            rs.Fields(0).Value = textboxIDNumber.Text
            rs.Fields(1).Value = textboxLastName.Text
            rs.Fields(2).Value = textboxFirstName.Text
            rs.Fields(3).Value = textboxMiddleName.Text
            rs.Update()
            MsgBox("Successfully Added!", MsgBoxStyle.Information, "Message")
        End If
    End If
        conn.Close()

I want to add an auto generated id number of a certain individual using YEAR and 4 digit integer to be exact.

    Dim idnumber As Integer
    Dim yrVal As String = DateTime.Now.Year
    idnumber = yrVal + "0001"

What I want is that if I register a person for the first time then his/her id number is 20180001 and the second person is 20180002, 0003, 004 and so on, but when the year is 2019 then it start again with 20190001, 20190002, 20190003 and so on. In my code as shown above it will increment from 20180001 to 20189999. I'm a beginner to this and it's hard to figure out.

How to generate Custom ID

I tried the solution link above but It is using OLeDBAdapter, dataset, datagrid and I don't know how to convert it to ADODB. I added ADODB reference. I'm a beginner and a little bit confused.

标签： vb.netms-accessado.net

解决方案

这很容易......第一次，使用以下代码生成一个 id：

Dim idnumber As Integer
Dim yrVal As String = DateTime.Now.Year
idnumber = yrVal + "0001"

''''Insert the data, I am not adding the inserting code :)

现在，为了生成第二个 id，我们需要首先获取我们插入数据库的最后一个 id。为此，请使用以下代码：

Dim LastId as Integer
Dim FirstIdCmd as New SqlCommand("SELECT TOP 1 * FROM TableName ORDER BY ID DESC",connection) 'Here ID is the column name and DESC stands for descending
Dim dr as SqlDataReader = FirstIdCmd.ExecuteReader
While dr.Read
 '''Here we get the last generated I
 Dim lstId as String = dr(0).ToString.Substring(4, 4)
End While

现在专注于dr(0).ToString.Substring(4,4)行。Strng.Substring方法用于提取字符串的特定部分。该方法接受 2 个整数变量值。第一个是StartingIndex，最后一个是Length。由于我们只需要获取 ID 的最后 3 位数字并且需要绕过前 4 位数字，因此我们设置StartIndex为 4 和Length4。所以，让我们再次回到编码：

While dr.Read
''Here,instead of creating a new string variable, we pass the value(with +1) to the declared Integer variable called LastID
 LastId = Convert.ToInt32(dr(0).ToString.Substring(4, 4)) + 1
End While

现在您可以使用 ID 并将其插入。希望这对您有所帮助。

推荐阅读