如何在VB.NET中使用年份和整数生成自定义ID [英] How do I generate custom ID using year and integer in VB.NET

问题描述

我想使用年份和整数制作自定义ID号。这是我的代码：

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()

我想使用YEAR和4位整数来添加某个人的自动生成的id号。

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"

我想要的是，如果我第一次注册一个人，那么他/她的身份证号码是20180001，第二个人是20180002，0003,004等等，但当年份是2019年，那么它又重新开始有20190001,20190002,20190003等。在我上面的代码中，它将从20180001增加到20189999.我是初学者，这很难弄明白。



我有什么试过：



https://www.codeproject.com/Questions/1039035/How-do-I-generate-auto-number-in- vb-net



vb.net - 如何生成 自定义ID - 堆栈溢出[ ^ ]



我尝试过上面的解决方案链接因为它与我的系统有点关联但它使用的是OLeDBAdapter，数据集，数据网格，我不知道如何将其转换为ADODB。我添加了ADODB参考。我是初学者，有点困惑。

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.

What I have tried:

https://www.codeproject.com/Questions/1039035/How-do-I-generate-auto-number-in-vb-net

vb.net - How to generate Custom ID - Stack Overflow[^]

I tried the solutions link above because its a little related to my system 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.

推荐答案

这样做的一种方法是创建一个包含下一个空闲号码的表。在最简单的形式中，表格列已经包含年份，因此价值可能类似于

One way to do this would be to create a table containing the next free number. In the simplest form the table column would already contain the year so the value could be like

20180019

现在每次添加新行时逻辑都会跟随

- 开始交易

- 更新免费号码行，加1吧

- 从表中选择值

- 使用所选值插入新行

- 提交交易。



但是我没有建议在目标表中使用这样的列。如果您每年需要超过9999行，会发生什么？您需要添加不同类型的检查并可能更改数据库架构。



相反，我建议使用代理键 - 维基百科 [ ^ ]



作为旁注，因为您将UI中的值直接连接到SQL语句，所以您的代码很容易受到SQL注入的攻击。请参阅 SQL注入 - 维基百科 [ ^ ]首选方法是使用参数，看看 CreateParameter Method（ADO）| Microsoft Docs [ ^ ]

Now each time a new row is added the logic would be following
- Start a transaction
- Update the free number row, add 1 to it
- Select the value from the table
- Insert the new row using the selected value
- Commit the transaction.

However I woudn't advice to use such column in the target table. What happens if you need more than 9999 rows in the table per year? You would need to add different kinds of checks and possibly change the database schema.

Instead I would recommend using a Surrogate key - Wikipedia[^]

As a side note, because you concatenate values from UI directly to the SQL statement your code is vulnerable to SQL injections. See SQL injection - Wikipedia[^] The preferred way is to use parameters, have a look at CreateParameter Method (ADO) | Microsoft Docs[^]

这篇关于如何在VB.NET中使用年份和整数生成自定义ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！