Failed to convert string to Int32

Question

I'm asp.net developer. I was executing stored procedure and getting column count reading with ExecuteScaler method. but no success it's give type cast error like this:

i have this code :

 protected int isValidUser()
        {
            try
            {
                return Convert.ToInt32(Mysql.ExecuteScalar("Support_UserLogin", AppGlobal.UserName, AppGlobal.Password));
            }
            catch (Exception ex)
            {
                AppGlobal.QLog.Enqueue(new clsLogType() { Message = System.Reflection.MethodBase.GetCurrentMethod().Name, ex = ex, logType = LogType.Error });
                return 0 ;
            }
        }

this is my stored proc code:

USE [NEWBULKSMS]
GO
/****** Object:  StoredProcedure [dbo].[Support_UserLogin]    Script Date: 01/03/2019 2:12:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Support_UserLogin] 

    @Username   varchar(100),
    @Password varchar(100)

AS
BEGIN

    BEGIN TRY

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select count(*) from users  where strUserName = @Username and strPassword = @password;

    END TRY
    BEGIN CATCH
        EXEC [dbo].[usp_LogErrorHistory];

        DECLARE @err VARCHAR(MAX)
        SELECT @err = ERROR_MESSAGE()

        RAISERROR('Error while getting : [dbo].[Support_UserLogin]', 16, 1, @err)
    END CATCH

END

after that done i'm getting error like Failed to convert string to Int32

what's wrong with this....

---------------------------------Updated-------------------------------------

When is run this stored proc in SSMS this returns me 1 in no column name.

USE [NEWBULKSMS]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[Support_UserLogin]
        @Username = N'shalingajjar',
        @Password = N'shalsoft$3433'

SELECT  'Return Value' = @return_value

GO

------------------------------------Updated------------------------------------

here i include ExecuteScaler code more info

#region ExecuteScalar

    public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        using (SqlConnection cn = new SqlConnection(ConString))
        {
            cn.Open();
            return ExecuteScalar(cn, commandType, commandText, commandParameters);
        }
    }
    public static object ExecuteScalar(string spName, params object[] parameterValues)
    {
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            SqlParameter[] commandParameters = GetSpParameterSet(spName);
            AssignParameterValues(commandParameters, parameterValues);
            return ExecuteScalar(CommandType.StoredProcedure, spName, commandParameters);
        }
        else
        {
            return ExecuteScalar(CommandType.StoredProcedure, spName);
        }
    }
    public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        SqlCommand cmd = new SqlCommand();
        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
        object retval = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        return retval;

    }

    private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
    {
        if(connection.State != ConnectionState.Open)
        {
            connection.Open();
        }

        command.Connection = connection;

        command.CommandText = commandText;
        if (transaction != null)
        {
            command.Transaction = transaction;
        }
        command.CommandType = commandType;

        if (commandParameters != null)
        {
            AttachParameters(command, commandParameters);
        }

        return;
    }
    private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
    {
        foreach (SqlParameter p in commandParameters)
        {
            if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
            {
                p.Value = DBNull.Value;
            }
            command.Parameters.Add(p);
        }
    }
    private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
    {
        if ((commandParameters == null) || (parameterValues == null))
        {
            return;
        }
        if (commandParameters.Length < parameterValues.Length)
        {
            throw new ArgumentException("Parameter count does not match Parameter Value count.");
        }

        for (int i = 0, j = commandParameters.Length; i < j; i++)
        {
            if (i < parameterValues.Length)
            {
                if (commandParameters[i].DbType.ToString() == "String")
                    commandParameters[i].Value = (parameterValues[i] == null ? "" : parameterValues[i]);
                else
                    commandParameters[i].Value = parameterValues[i];
            }
            else
            {
                break;
            }

        }
    }

    #endregion

Answer 1

ExecuteScalar returns the first column of the first row in the first result set. https://dev.mysql.com/doc/dev/connector-net/8.0/html/M_MySql_Data_MySqlClient_MySqlCommand_ExecuteScalar.htm

If the stored proc has nothing else in it, it should work. But I see you have comment "-- Insert statements for procedure here" in the stored proc. So those might be causing issues?

The way you executed the stored proc in SSMS will return something different to what you expect from your .Net code. I was expecting to see 1 returned with no column name (that is your select count(*)...) and a 0 returned with name Return Value.

Lastly, if the stored proc is doing exactly what you have posted

1. Will it make more sense to use function instead? You can return your count(*) there.
2. Do you really care about number of records there? will it make more sense to do something like "if exists(select 1 from users where strUserName = @Username and strPassword = @password) return 1" it will be more efficient.

Answer 2

Don't have enough reputation to comment.

Try changing your code to something like this:

protected int isValidUser()
{
    try
    {
        var returnedValue = Mysql.ExecuteScalar("Support_UserLogin", AppGlobal.UserName, AppGlobal.Password);
        Console.Writeline(returnedValue);//this line is to view what was actually returned.
        return Convert.ToInt32(returnedValue );
    }
    catch (Exception ex)
    {
        AppGlobal.QLog.Enqueue(new clsLogType() { Message = System.Reflection.MethodBase.GetCurrentMethod().Name, ex = ex, logType = LogType.Error });
        return 0 ;
    }
}

If you debug that you may see more clearly why the value is not a valid integer (it could be a long, or something else)