vb.net 使用存储过程返回 Max
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18714919/
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
Returning Max with a stored procedure
提问by indofraiser
I have read and retried rebuilding the below in many ways but to keep it clear I will show my last attempt.
我已经阅读并重试以多种方式重建以下内容，但为了清楚起见，我将展示我的最后一次尝试。
Aim - To get the Max value of column "UniqueID"
目的 - 获取列“UniqueID”的最大值
The column field 'uniqueID' is set as a bigint
列字段 'uniqueID' 设置为 bigint
I assume the error is in the line with addwithvalue in as I get "int" as the return value
我假设错误与 addwithvalue in 一致，因为我将“int”作为返回值
If I run the query SELECT MAX(UniqueID) FROM tblResults in SQLit works
如果我运行查询SELECT MAX(UniqueID) FROM tblResults in SQL它的工作原理
Code
代码
Dim connection5 As SqlConnection
Dim command5 As New SqlCommand
Dim ds5 As New DataSet
Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
connection5 = New SqlConnection(ConnectionString5)
connection5.Open()
command5.Connection = connection5
command5.Parameters.Clear()
command5.CommandText = "spUniqueUserID"
command5.Parameters.AddWithValue("@UniqueID", SqlDbType.BigInt)
command5.Parameters("@UniqueID").Direction = ParameterDirection.Output
command5.CommandType = CommandType.StoredProcedure
command5.ExecuteNonQuery()
Session.Item("UniqueID") = command5.Parameters("@UniqueID").Value
connection5.Close()
Dim vShow As String
vShow = ""
vShow = Session.Item("UniqueID").ToString
SP
服务提供商
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[spUniqueUserID] Script Date: 09/10/2013 08:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUniqueUserID]
@UniqueID bigint OUTPUT
AS
BEGIN
select @UniqueID = (SELECT MAX(UniqueID) FROM tblResults )
Return @UniqueID
END
回答by jenson-button-event
I personally wouldnt mess around with the output parameter. Simply use
我个人不会弄乱输出参数。只需使用
ALTER PROCEDURE [dbo].[spUniqueUserID]
AS
BEGIN
SELECT MAX(UniqueID) FROM tblResults
END
in your proc, and
在你的过程中，和
Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "spUniqueUserID"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1
sqlConnection1.Open()
returnValue = cmd.ExecuteScalar()
sqlConnection1.Close()
In your code. (best with using statements for your connection and command, here missing, for brevity)
在你的代码中。（最好为您的连接和命令使用语句，为简洁起见，此处省略）
回答by Steve
Try with
试试
command5.Parameters.AddWithValue("@UniqueID", 0L)
The AddWithValue determines the DataType of the parameter to pass to the underlying engine looking at the datatype of the second parameter. You pass a string and this is certainly wrong.
AddWithValue 确定要传递给查看第二个参数数据类型的底层引擎的参数的数据类型。你传递一个字符串，这肯定是错误的。
In alternative you could define explicitly the parameter
或者，您可以明确定义参数
Dim parameter = new SqlParameter("@UniqueID", SqlDbType.BigInt)
parameter.Direction = ParameterDirection.Output
parameter.Size = 8
parameter.Value = 0
command5.Parameters.Add(parameter)
And, as last but fundamental steps, don't forget to specify that this command executes a storedprocedure and then execute the command
并且，作为最后但基本的步骤，不要忘记指定此命令执行存储过程然后执行命令
command5.CommandType = CommandType.StoredProcedure
command5.ExecuteNonQuery()
As an alterative, but this requires a change to your stored procedure, is to use ExecuteScalar. This method should be used when you need a single result from your code.
作为替代方案，但这需要更改您的存储过程，是使用 ExecuteScalar。当您需要代码中的单个结果时，应使用此方法。
ALTER PROCEDURE [dbo].[spUniqueUserID]
AS
BEGIN
select SELECT MAX(UniqueID) FROM tblResults
END
And in your code
在你的代码中
Using connection5 = New SqlConnection(ConnectionString5)
Using command5 As New SqlCommand("spUniqueUserID", connection5)
connection5.Open()
command5.CommandType = CommandType.StoredProcedure
Dim result = command5.ExecuteScalar()
.....
End Using
End Using
But at this point the usefulness of the storedprocedure is really minimal and you could code directly the sql in the constructor of the SqlCommand and remove the CommandType setting
但是此时存储过程的用处真的很小，您可以直接在 SqlCommand 的构造函数中编写 sql 并删除 CommandType 设置
Using connection5 = New SqlConnection(ConnectionString5)
Using command5 As New SqlCommand("SELECT MAX(UniqueID) FROM tblResults", connection5)
connection5.Open()
Dim result = command5.ExecuteScalar()
.....
End Using
End Using
回答by indofraiser
Dim ConnectionString5 As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQLConnectionString").ToString()
Using connection5 = New SqlConnection(ConnectionString5)
Using command5 As New SqlCommand("SELECT MAX(UniqueID) FROM tblResults", connection5)
connection5.Open()
Dim result = command5.ExecuteScalar()
Session.Item("UniqueID") = result
connection5.Close()
End Using
End Using
Dim vShow As String
vShow = ""
vShow = Session.Item("UniqueID").ToString
