Home / Questions / 23811
Accepted
Sagara
Asked: 2020-09-15 08:55:58 +0800 CST 2020-09-15 08:55:58 +0800 CST

How to save data from a query in a stored procedure from VisualBasic.Net?

  • 772

I need to create a Login with user types compulsorily with stored procedures in SQL Server. I have not been able to capture the user type id.

Types of users:

1 = Administrador
2 = Secretaria
3 = Profesional

The stored procedure is:

create procedure ident (@rut varchar(10), @contrasena varchar(30))
as
begin
select ID_TIPO_USUARIO from USUARIO
where RUT_USUARIO = @rut and CONTRASENA = @contrasena
end

And in VisualBasic I have the following

Dim rut, contrasena As String
rut = tbxRut.Text
contrasena = tbxContraseña.Text

Using cnn As SqlConnection = New SqlConnection("Initial Catalog=CONSULTORIO;" & _
"Data Source=DESKTOP-LGTPUJM\SQLEXPRESS;Integrated Security=SSPI;")

    Try
        cnn.Open()
        cmd = New SqlCommand("ident", cnn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@rut", rut)
        cmd.Parameters.AddWithValue("@contrasena", contrasena)

        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox("Error")
    End Try

End Using

The stored procedure executes without errors, but I don't know how to capture the query log

sql-server

5 Answers

  1. Note that you are using ExecuteNonQuery, which implies that you are executing a query that is not going to return a result.

    You can easily get the result of the query, using for example ExecuteScalar(), which will return you directly ID_TIPO_USUARIO.

    Or if you want to get the full result, you should use ExecuteReader().

    • 4

  2. To get the answer you must use the

    SqlCommand.ExecuteScalar() method

    then it would stay

    cmd = New SqlCommand("ident", cnn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@rut", rut)
cmd.Parameters.AddWithValue("@contrasena", contrasena)

Dim tipo As Integer = Convert.ToInt32(cmd.ExecuteNonQuery())

    Note: the "type" variable should be defined outside of the using

    • 2
  3. Best Answer

    It's ok just need to change one thing

    Try
    dim Codigo integer

    cnn.Open()
    cmd = New SqlCommand("ident", cnn)
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@rut", rut)
    cmd.Parameters.AddWithValue("@contrasena", contrasena)

    Codigo = cmd.ExecuteScalar()
Catch ex As Exception
    MsgBox("Error")
End Try
    • 1

  4. I would like to contribute one more alternative to the very good answers that this interesting question has had.

    If we want to use ExecuteNonQuery(), we can use parameters out, so that SQL Server/Express returns a result to us in said parameter.

    Then we should include one more parameter to the stored procedure, but specifying that it is an output parameter ( out):

    create procedure ident (@rut varchar(10), @contrasena varchar(30), @id_tipo_usuario int out)
as
begin
select @id_tipo_usuario = ID_TIPO_USUARIO from USUARIO
where RUT_USUARIO = @rut and CONTRASENA = @contrasena
end

    In SELECTit we must assign the result of the query to the output parameter:@id_tipo_usuario = ID_TIPO_USUARIO

    So the code in VB.NET could be:

    Dim rut, contrasena As String
rut = tbxRut.Text
contrasena = tbxContraseña.Text

Try
    Using cnn As New SqlConnection("Initial Catalog=CONSULTORIO;" & _
                                   "Data Source=DESKTOP-LGTPUJM\SQLEXPRESS;" & _
                                   "Integrated Security=SSPI;")
        cnn.Open()
        Using cmd As New SqlCommand("ident", cnn) With {.CommandType = CommandType.StoredProcedure}

            cmd.Parameters.AddWithValue("@rut", rut)
            cmd.Parameters.AddWithValue("@contrasena", contrasena)

            ' Agregamos el parámetro de salida para SQL Server/Express, usando la propiedad Direction.
            cmd.Parameters.Add("@id_tipo_usuario", SqlDbType.Int).Direction = ParameterDirection.Output
            ' También se puede usar .AddWithValue(), pero quise ahorrar una variable.

            cmd.ExecuteNonQuery()

            ' El resultado que devuelva el procedimiento almacenado en su parámetro de salida, se guardará en la colección Parameters.
            MsgBox("id_tipo_usuario = " & cmd.Parameters("@id_tipo_usuario").Value.ToString)
            ' Aquí se nota que tal vez sería mejor el uso de una variable.

        End Using
    End Using

Catch ex As Exception
    MsgBox(ex.ToString, MsgBoxStyle.Critical, "Error")
End Try

    As you can see, I put the Tryenglobing to Using, because it seems to me that the other way around is wrong.

    • 0

  5. you can put your sp el SET NOCOUNT OFF, so executeNoQuery()it returns the number of affected rows

    • -1