在 vb.net 中自动增加 nvarchar 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14669252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto Increment nvarchar value in vb.net
提问by coder
I'm having a UserIDfield in my table and I need to get that value in my app and whenever I would like to add a new record it should increment by value '1'.
我的表中有一个UserID字段，我需要在我的应用程序中获取该值，每当我想添加新记录时，它都应该按值 '1' 递增。
So this is how I'm trying to get the last IDenetered from my table.
所以这就是我试图从我的表中获取最后一个 ID 的方式。
For ExampleI'm having a value as "A000"and I need to increment that value by '1' so that it should become "A001"and so on..and after 'A999'as pointed out by PinnyM' it should become 'A1000'.
例如，我有一个值为“A000”，我需要将该值增加“1”，以便它应该变成“A001”等等......并且在PinnyM指出的“A999”之后它应该变成'A1000'。
I don't want to write any stored procedures or anyother way from database.I would like to do it in a simpler way using my existing code.
我不想从数据库中编写任何存储过程或任何其他方式。我想使用我现有的代码以更简单的方式来完成。
Dim strConnection As String = "Data Source=.\SqlExpress;Initial Catalog=Subscription;Integrated Security=True"
Dim con As New SqlConnection(strConnection)
con.Open()
Dim comm As New SqlCommand
comm.CommandText = "SELECT MAX(UserID) FROM Customers"
comm.Connection = con
Dim MaxUserID As Object = comm.ExecuteScalar()
txtID.text=MaxUserID
采纳答案by coder
I got it managed to work by doing it this way:
我通过这样做使其成功工作：
Public Function IncrementString(ByVal Sender As String) As String
Dim Index As Integer
For Item As Integer = Sender.Length - 1 To 0 Step -1
Select Case Sender.Substring(Item, 1)
Case "000" To "999"
Case Else
Index = Item
Exit For
End Select
Next
If Index = Sender.Length - 1 Then
Return Sender & "1" ' Optionally throw an exception ?
Else
Dim x As Integer = Index + 1
Dim value As Integer = Integer.Parse(Sender.Substring(x)) + 1
Return Sender.Substring(0, x) & value.ToString()
End If
End Function
回答by PinnyM
Based on the comments above, I would propose:
根据以上意见，我建议：
comm.CommandText = "SELECT 'A' + RIGHT('000', CAST((MAX(primary_key) + 1) as nvarchar(255)), 3) FROM Customers"
What this does is:
它的作用是：
- Get the last key and add 1
- Cast to nvarchar
- Pad with 0's if necessary (up to 3 places)
- 获取最后一个键并加1
- 转换为 nvarchar
- 如有必要，用 0 填充（最多 3 个位置）
I would absolutely nottry to do this using a character based key as the UserIDappears to be built. Also, note that this is not atomic and doesn't guarantee that another connection won't insert a row before you do. So it's really returning an optimistic value that may not be actually be correct. If this doesn't work for you, then consider letting IDENTITY do its work for you and get the value after the row was created using SCOPE_IDENTITY()
我绝对不会尝试使用基于字符的键来执行此操作，因为它UserID似乎是构建的。另外，请注意，这不是原子操作，并且不能保证另一个连接不会在您之前插入一行。因此，它确实返回了一个可能实际上并不正确的乐观值。如果这对您不起作用，请考虑让 IDENTITY 为您工作并在使用创建行后获取值SCOPE_IDENTITY()
