Register with output variable using ASP.Net and SQL Server

3

I'm doing a web application with the ASP.Net C # language but I'm validating the OUT output to show me the error message:

This is my procedure:

ALTER PROCEDURE pAdminUsersEvaSmart @tipo INT
    ,@UserName VARCHAR(100)
    ,@ERROR VARCHAR(100) OUT
AS
BEGIN
    IF @tipo = 0
    BEGIN
        IF EXISTS (
                SELECT *
                FROM seguridad..Usuario
                WHERE username = @UserName
                )
        BEGIN
            UPDATE seguridad..Usuario
            SET IntentosUltimoAcceso = 0
                ,IntentosActualAcceso = 0
                ,Conexion = 'DES'
            WHERE UserName = @UserName

            SET @ERROR = 'SE DESBLOQUEO CORRECTAMENTE..'
        END
        ELSE
        BEGIN
            SET @ERROR = 'NO SE PUEDE DESBLOQUEAR EL USUARIO NO EXISTE..'
        END
    END
END

ASP.Net Code:

protected void btnDesbloqueo_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Server=10.0.101.85\instbdd01;Database=Seguridad;User Id=sa;Password=SA123456789*;");
    SqlCommand cmd = new SqlCommand("pAdminUsersEvaSmart", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@tipo", SqlDbType.Int);
    cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 100);
    cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);

    cmd.Parameters["@tipo"].Value = 0;
    cmd.Parameters["@UserName"].Value = txtUserName.Text;
    cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;

    conn.Open();
    cargardatos();

    cmd.ExecuteNonQuery();
    conn.Close();

    Label1.Text = cmd.Parameters["@ERROR"].Value.ToString(); ;

    if (Label1.Text == "SE DESBLOQUEO CORRECTAMENTE..")
    {
        ScriptManager.RegisterStartupScript(this, GetType(), "Show Modal Popup", "showmodalpopup1();", true);
    }
    else if (Label1.Text == "NO SE PUEDE DESBLOQUEAR EL USUARIO NO EXISTE..")
    {
        ScriptManager.RegisterStartupScript(this, GetType(), "Show Modal Popup", "showmodalpopup();", true);
    }
}

As you can see if it has the same name by which you should enter that if but do not enter I do not understand why they are the same chains.

    
asked by PieroDev 06.04.2017 в 18:13
source

3 answers

1

Initially, I would make a small restructuring of your Stored Procedure so that you do not have an exit message:

ALTER PROCEDURE pAdminUsersEvaSmart @tipo INT
    ,@UserName VARCHAR(100)
AS
BEGIN
    IF @tipo = 0
    BEGIN
        IF EXISTS (
                SELECT 1
                FROM seguridad..Usuario
                WHERE username = @UserName
                )
        BEGIN
            UPDATE seguridad..Usuario
            SET IntentosUltimoAcceso = 0
                ,IntentosActualAcceso = 0
                ,Conexion = 'DES'
            WHERE UserName = @UserName

            SELECT 'SE DESBLOQUEO CORRECTAMENTE..'
        END
        ELSE
        BEGIN
            SELECT 'NO SE PUEDE DESBLOQUEAR EL USUARIO NO EXISTE..'
        END
    END
END

Now, on the side of your C # code, you get the message with ExecuteScalar , the message is validated and assigned to Label1 :

string resultado = string.Empty;

SqlConnection conn = new SqlConnection("Server=10.0.101.85\instbdd01;Database=Seguridad;User Id=sa;Password=SA123456789*;");
SqlCommand cmd = new SqlCommand("pAdminUsersEvaSmart", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 100);

cmd.Parameters["@UserName"].Value = txtUserName.Text;

conn.Open();
cargardatos();

resultado = cmd.ExecuteScalar().ToString();
conn.Close();

if(resultado == "SE DESBLOQUEO CORRECTAMENTE..")
{
    Label1.Text == "SE DESBLOQUEO CORRECTAMENTE..";
    ScriptManager.RegisterStartupScript(this, GetType(), "Show Modal Popup", "showmodalpopup1();", true);
}
else
{
    Label1.Text == "NO SE PUEDE DESBLOQUEAR EL USUARIO NO EXISTE..";
    ScriptManager.RegisterStartupScript(this, GetType(), "Show Modal Popup", "showmodalpopup();", true);
}
    
answered by 06.04.2017 / 21:51
source
2

If you can notice in the image that you send of the debugging, it is not possible to visualize where the quotes are closing for the value of Label1.Text . This means that SQL SERVER is sending you the text with blank spaces to the right. That said

"SE DESBLOQUEO CORRECTAMENTE.." siempre va a ser diferente a "SE DESBLOQUEO CORRECTAMENTE..            "

To correct this, simply change this line of code

Label1.Text = cmd.Parameters["@ERROR"].Value.ToString(); ;

For this one, that removes the blank spaces from string

Label1.Text = cmd.Parameters["@ERROR"].Value.ToString().Trim();
    
answered by 06.04.2017 в 18:37
0

It is always recommended to make these comparisons by number or error code instead of a string but by what you see in your LABEL1.Text your string does not end in the ".." apparently so it looks continuous with spaces at the end that is why when comparing the strings they are not matching.

    
answered by 06.04.2017 в 18:36