vb.net 如何获取表中的最后一个自动增量值？网络

How to get last auto increment value in a table? VB.NET

提问by Thahleel Abid

How would you get the last Primary Key/Auto Increment value in a table using OleDb?

您将如何使用 OleDb 获取表中的最后一个主键/自动增量值？

I need to get this value so I can create a folder for a record before it is added so that files can be copied to the folder when it is added.

我需要获取此值，以便在添加记录之前为记录创建一个文件夹，以便在添加文件时将文件复制到该文件夹​​中。

Any idea? I have tried as following.

任何的想法？我试过如下。

@@Identity'Need to insert a record first and I can't do that without copying the files first SELECT SCOPE_IDENTITY()'Doesn't work with OleDb

@@Identity'需要先插入一条记录，如果不先复制文件，我就不能这样做 SELECT SCOPE_IDENTITY()'不适用OleDb

This is the error message I get:

这是我收到的错误消息：

采纳答案by MarkHoward02

I think this might work:

我认为这可能有效：

SELECT MAX(ID) FROM MyTable

回答by Thilina H

you can do it like this because of The Jet 4.0 provider supports @@Identity, Reference

你可以这样做，因为 Jet 4.0 provider 支持@@Identity， 参考

Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("", Category.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.CommandText = query2
    ID = cmd.ExecuteScalar()
  End Using
End Using

回答by Al-3sli

You can try Check if NULL first :

您可以先尝试检查是否为 NULL：

Select if(IsNull(Max(ColName)),1,Max(ColName) + 1 ) From YourTable

回答by Abdul Waheed

try this (vb.net)
'''
Dim lastrecord As Integer
    Dim command As New SqlCommand("Select IDENT_CURRENT('tbluom')+1", conn)
    command.ExecuteNonQuery()
    Dim dt As New DataTable()
    Dim da As New SqlDataAdapter(command)
    lastrecord = command.ExecuteScalar()
    txt_uomid.Text = lastrecord
    MsgBox(lastrecord)
    Dim encode As String = txt_uomid.Text '"99999"
    Dim encint As Integer = Integer.Parse(encode) '+ 1
    encode = "00" & "-" & encint.ToString("00000").Substring(1, 4)
    MsgBox(encode)
    ''''

回答by Vignesh Kumar A

Try this

尝试这个

Select IDENT_CURRENT('TableName')

It Will retrun Last ID(If it's Auto increment) of your Table

它将重新运行您的表的最后一个 ID（如果它是自动增量）

回答by Nagaraj S

reference

参考

**c#**
string query = "Insert Into Categories (CategoryName) Values (?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand(query, conn))
  {
    cmd.Parameters.AddWithValue("", Category.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    cmd.CommandText = query2;
    ID = (int)cmd.ExecuteScalar();
  }
}

**VB**
Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("", Category.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.CommandText = query2
    ID = cmd.ExecuteScalar()
  End Using
End Using

refer

参考