"There is no data available for the row or column" 尝试恢复 vb.net 表上插入的最后一个 ID 时出错

Question

我在恢复表上最后插入的id以在需要的地方进行其他插入的函数上遇到此问题。

这是我的函数代码:

Public Function BuscarUltimoidOrdenProd() As Integer
CONECTOR.Open()
Dim SQL As String = ("SELECT MAX(idOrdenProd) as idOrdenProd FROM ordenproduccion")
Dim cmd As New OleDbCommand(SQL, CONECTOR)
cmd.CommandType = CommandType.Text
Dim dr As OleDbDataReader = cmd.ExecuteReader()
Dim x As Integer = Val(dr("idOrdenProd").ToString)
Return x
CONECTOR.Close()
End Function 

这些是类中的连接变量:

Dim CONECTOR As New OleDbConnection("Data Source=DESKTOP-DNN8GMESQLEXPRESS; Initial Catalog=DB_Calcita;Provider=SQLOLEDB;Integrated Security=SSPI;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False")
Dim COMANDO As New OleDbCommand
Dim ADAPTADOR As New OleDbDataAdapter(COMANDO) 

我没有看到错误，而我执行查询(SELECT MAX(idOrdenProd)作为idOrdenProd FROM orderproduccion)在SQL管理工作室它检索正确的值。

这是我调用函数的代码:

Public Sub GenerarOrdenStockProd(idStockMP As Integer, idStockP As Integer, CantidadProducto As Decimal, CantidadMPrima As Decimal)
Dim FILA As DataRow = TABLA.NewRow
Dim objOrdProd As New OrdenProduccion
Dim x As Integer = objOrdProd.BuscarUltimoidOrdenProd
FILA("idStockMP") = idStockMP
FILA("idStockP") = idStockP
FILA("CantidadProducto") = CantidadProducto
FILA("CantidadMPrima") = CantidadMPrima
FILA("idOrdenProd") = x
TABLA.Rows.Add(FILA)
Actualizar()
End Sub 

Answer 1

你做错了。您不需要查询表的最大ID值。向数据库询问最近自动生成的值。如果你在相同的范围内做，你可以用SCOPE_IDENTITY或者你可以用@@IDENTITY。后者是全局的，因此在多用户环境中可能是一个问题。您可以执行单独的SELECT @@IDENTITY语句，也可以在与INSERT相同的命令中包含SELECT SCOPE_IDENTITY()语句。