从 SQL Server 返回值到 VB.Net-解网

问：

如何传回我的 VB 过程并让读者阅读它？我不断收到错误：@attackPercentage

System.IndexOutOfRangeException：攻击百分比

法典：

ALTER PROCEDURE [dbo].[SelectPlayersRating]
    @playerAccountID uniqueidentifier,
    @raterAccountID uniqueidentifier
AS
BEGIN
    DECLARE @attackPercentage INT

    SELECT attack, safety, consistency 
    FROM tblRatings 
    WHERE @playerAccountID = playerAccountID 
      AND @raterAccountID = raterAccountID

    SET @attackPercentage = '99' --Test Value

    RETURN @attackPercentage 
END

VB.NET 代码：

Dim DBConnect3 As New DBConn

Using db As DbConnection = DBConnect3.Conn("DBConnectionString")

    Dim cmd As SqlCommand = DBConnect3.Command(db, "SelectPlayersRating")
    cmd.Parameters.Add(New SqlParameter("playerAccountID", SqlDbType.UniqueIdentifier, ParameterDirection.Input)).Value = Guid.Parse(Request.QueryString("aID"))
    cmd.Parameters.Add(New SqlParameter("raterAccountID", SqlDbType.Uniqueidentifier, ParameterDirection.Input)).Value = acc.accountID

    db.Open()

    Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

    While DR.Read
        ddlAttack.SelectedValue = DR("attack")
        ddlSafety.SelectedValue = DR("safety")
        ddlConsistency.SelectedValue = DR("consistency")
        session("test") = DR("attckPercentage")
    End While

    DR.Close()
    DR = Nothing
    cmd.Dispose()
    cmd = Nothing
    db.Dispose()
    db.Close()
End Using

sql-server vb.net

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt = GetAttackData(Guid.Parse(Request.QueryString("aID")), acc.accountID)
    ddlAttack.SelectedValue = dt(0)("attack")
    ddlSafety.SelectedValue = dt(0)("safety")
    ddlConsistency.SelectedValue = dt(0)("consistency")
    session("test") = 99 'You can calculate this here instead of the stored procedure
End Sub

我不确定您为什么要将 DbConnection 用于 Sql Server。不要从其他地方获得连接。需要关闭和释放连接，因此请使用局部变量。

Private Function GetAttackData(aId As Guid, accID As Guid) As DataTable
    Using dt As New DataTable
        Using db As New SqlConnection(ConStr),
        cmd As New SqlCommand("SelectPlayersRating", db)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("playerAccountID", SqlDbType.UniqueIdentifier).Value = aId
            cmd.Parameters.Add("raterAccountID", SqlDbType.UniqueIdentifier).Value = accID
            db.Open()
            Using DR As SqlDataReader = cmd.ExecuteReader()
                dt.Load(DR)
            End Using
        End Using
        Return dt
    End Using
End Function

2赞 Lionized 1/9/2021 #2

根据存储过程，查询可以返回多个结果，因此可以返回 While DR。阅读语句。

但是，每次返回存储过程时，attackPercentage 的值将返回一个值。因此，您可以使用输出参数从存储过程中提取值：

ALTER PROCEDURE [dbo].[SelectPlayersRating]
@playerAccountID uniqueidentifier,
@raterAccountID uniqueidentifier
, @attachPercentage INT output --  This is the new output parameter
AS
BEGIN
-- DECLARE @attackPercentage INT -- remove the declaration as we've defined it already

SELECT attack, safety, consistency 
FROM tblRatings 
WHERE @playerAccountID = playerAccountID 
  AND @raterAccountID = raterAccountID

SET @attackPercentage = '99' --Test Value

-- RETURN @attackPercentage  -- No need to return this value
END

那么现在，如何获取返回值呢？只需将输出参数添加到参数集合中：

  Dim cmd As SqlCommand = DBConnect3.Command(db, "SelectPlayersRating")
cmd.Parameters.Add(New SqlParameter("playerAccountID", SqlDbType.UniqueIdentifier, ParameterDirection.Input)).Value = Guid.Parse(Request.QueryString("aID"))
cmd.Parameters.Add(New SqlParameter("raterAccountID", SqlDbType.Uniqueidentifier, ParameterDirection.Input)).Value = acc.accountID
cmd.Parameters.Add(New SqlParameter("attackPercentage", SqlDbType.Int, ParameterDirection.Output))

调用 ExecuteReader 后，可以简单地说：

session("test") = cmd.Parameters("attackPercentage").Value

0赞 Gwilym Owen 9/27/2023 #3

首先，如果删除将 attackPercentage 设置为 99 的引号，它应该可以工作。这似乎毫无意义，也不像你试图做的事情，但这就是导致抛出异常的原因。

而不是： SET @attackPercentage = '99' --测试值

试试这个： SET @attackPercentage = 99 -- 无引号

我想有人回答了您如何将参数传递给查询，但是......我对 VB 相当陌生，所以我仍在努力。

从 SQL Server 返回值到 VB.Net

Returning a value from SQL Server to VB.Net

评论

评论