Stored procedure that returns an integer value

Question

Stored procedure that returns an integer value

Navigation

#1 by (1 votes)

1

I have the following code and what I want to do is that when asking for a Stored Procedure, that if I have a specific record in my Database and it returns a whole value and how to use that whole value in Visual Basic, I have I've been reading forums, but I can not find my doubt, thank you in advance.

CREATE PROC PAValidarUsuario
    @Usuario varchar(30),
    @Contrasena varchar(30),
    @TipoUsuario varchar(15),
    @Valor int output
    as
    if exists(select * from TBUsuario where Usuario=@Usuario and Contrasena=@Contrasena and TipoUsuario=@TipoUsuario)
    begin
        set @Valor=1
    end
    else
    begin
        set @Valor=2
    end

And this is the function of my Visual Basic

Public Function ValidarUsuario(ByVal Datos As CUsuario)
    Try
        Conectado()
        CMD = New SqlCommand("PAValidarUsuario")
        CMD.CommandType = CommandType.StoredProcedure
        CMD.Connection = Conexion
        CMD.Parameters.AddWithValue("@Usuario", Datos.GetUsuario)
        CMD.Parameters.AddWithValue("@Contrasena", Datos.GetContrasena)
        CMD.Parameters.AddWithValue("@TipoUsuario", Datos.GetTipoUsuario)

        If CMD.ExecuteNonQuery Then
            Dim Num As Integer
            Num = ...
            'En ese Num, quiero obtener lo que retorne de mi Procedimiento Almacenado, pero no doy con el código
            Return Num
        Else
            Return Nothing
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
        Return Nothing
    End Try
End Function

Please tell me the mistakes or what I should change, thank you very much.

vb.net

asked by Andrés 03.07.2018 в 20:21

source

1 answer

Names of rows in an array in R Syntax PHP error ""

score 1 · Accepted Answer

    Public Function ValidarUsuario(ByVal Datos As CUsuario)
        Try
            Conectado()
            CMD = New SqlCommand("PAValidarUsuario")
            CMD.CommandType = CommandType.StoredProcedure
            CMD.Connection = Conexion
            CMD.Parameters.AddWithValue("@Usuario", Datos.GetUsuario)
            CMD.Parameters.AddWithValue("@Contrasena", Datos.GetContrasena)
            CMD.Parameters.AddWithValue("@TipoUsuario", Datos.GetTipoUsuario)
            CMD.Parameters.Add("@Valor", SqlDbType.Int)
            CMD.Parameters("@Valor").Direction = ParameterDirection.Output)

            If CMD.ExecuteNonQuery Then
                Dim Num As Integer
//                Num = ... 'En ese Num, quiero obtener lo que retorne de mi Procedimiento Almacenado, pero no doy con el código
              Num = Int.Parse(CMD.Parameters("@Valor").Value.ToString())
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        End Try
    End Function