I am trying to Login but I don't why the controller throws an error at ExecuteScalar of

Object reference is not set to an instance of an object

If anyone can help me with this then please let me know, and any suggestion for making this better will be appreciated

Here is my ajax code:

$('#login').click(function (ev) {
            ev.preventDefault();
    
            FieldValidation();
    
            var data = new Object();
            data.UserName = $('#username').val();
            data.UPassword = $('#userpass').val();
    
            if (data.UserName && data.UPassword) {
                $.ajax({
                    url: 'http://localhost:1089/api/Employee/IfExist',
                    type: "POST",
                    dataType: 'json',
                    contentType: "application/json",
                    data: JSON.stringify(data),
                    beforeSend: function () {
                        $("#dvRoomsLoader").show();
                    },
                    complete: function () {
                        $("#dvRoomsLoader").hide();
                    },
                    success: function (data) {
                        if (data.Role = "Admin")
                            window.location = "../Admin/Index";
                        if (data.Role = "Employee")
                            window.location = "../Employee/Index";
                    },
                    error: function (ex) {
                        alert('Error'   ex.responseXML);
                        alert('Error'   ex.responseText);
                        alert('Error'   ex.responseJSON);
                        alert('Error'   ex.readyState);
                        alert('Error'   ex.statusText);
                    }
                });
            }
            return false;
        });

This is what I have written down in my stored procedure:

SELECT * 
FROM Employee 
WHERE UserName = @UserName AND pass = @pass

UPDATE Employee
SET IsActive = 1
WHERE UserName = @UserName AND pass = @pass

This is the method I have created in controller to be called on ajax hit:

[HttpPost]
public bool IfExist(Employee emp)
{
    var con = DB.getDatabaseConnection();

    SqlCommand com = new SqlCommand("sp_CheckUserIfExists", con);
    com.CommandType = CommandType.StoredProcedure;

    #region If the User Exist then what to do Code Block

    com.Parameters.AddWithValue("@UserName", emp.UserName);
    com.Parameters.AddWithValue("@pass", emp.UPassword);

    // com.ExecuteScalar();
    int UserExist = (int)com.ExecuteScalar();

    #endregion

    if (UserExist > 0)
    {
        return true;
    }
    else
    {
        return false;
    }
}

CodePudding user response：

ExecuteScalar is usually used for Select queries and it return the first column of select. So change select to this

SELECT  Count(*) 
FROM Employee 
WHERE UserName = @UserName AND pass = @pass
....

but it is better to use just the second statement of your sp

UPDATE Employee
SET IsActive = 1
WHERE UserName = @UserName AND pass = @pass

and it will return number of affected rows, if you use executenonquery

 int rows= (int)com.ExecuteNonQuery();
 bool UserExist= rows >0;