Returning a value from SQL Server to VB.Net

Question

How do I pass @attackPercentage back to my VB procedure and have it read by the reader? I keep getting an error:

System.IndexOutOfRangeException: attackPercentage

Code:

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 code:

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

question from:https://stackoverflow.com/questions/65622858/returning-a-value-from-sql-server-to-vb-net

Answer 1

Based on your stored procedure, your query can return multiple results, hence your While DR.Read statement.

However, your value for attackPercentage will return one value every time the stored procedure is returned. So you can use an Output Parameter to extract the value from the stored procedure:

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

So now, how do you get the return value? Just add an output parameter to your parameter collection:

  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))

After your call to ExecuteReader, you can simply say:

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