CODEWITHSUNDEEP
c#sqlsql-serverado.netsqlparameter
afandre
afandre

Reputation: 11

SQLCommand parameter is throwing "InvalidCastException"

I have an old C# application that stopped working when updating the database using SqlCommand. I pull in a user object and update the values in a MSSQL database.

The error I'm receiving is below

EventSink 'command.EventSink' threw an exception of type 'System.InvalidCastException' System.Data.SqlClient.SqlCommand.CommandEventSink {System.InvalidCastException}

Here's the SqlConnection code.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Subcriptions"].ToString());

using (con)
{
    con.Open();
    StringBuilder builder1 = new StringBuilder();
    builder1.Append("UPDATE dbo.Subscribers SET ");
    builder1.Append("Name = @Name,");
    builder1.Append("Email = @Email,");
    builder1.Append("Company = @Company,");
    builder1.Append("Active = @Active ");
    builder1.Append("WHERE SiebelUserId = @UserId");
    SqlCommand command = new SqlCommand(builder1.ToString(), con);
    command.Parameters.Add("@UserId", SqlDbType.VarChar);
    command.Parameters.Add("@Name", SqlDbType.VarChar);
    command.Parameters.Add("@Email", SqlDbType.VarChar);
    command.Parameters.Add("@Company", SqlDbType.VarChar);
    command.Parameters.Add("@Active", SqlDbType.Bit);
    command.Parameters["@UserId"].Value = user.Username;
    command.Parameters["@Name"].Value = user.FirstName + " " + user.LastName;
    command.Parameters["@Email"].Value = user.Email;
    command.Parameters["@Company"].Value = user.CompanyName;
    command.Parameters["@Active"].Value = user.IsActive;
    try
    {
        int rowsAffected = command.ExecuteNonQuery();
        
        if (rowsAffected == 0)
        {
            Program.WriteLog(rowsAffected + " SQL Record not updated.");
        }
    }
    catch (Exception exception1)
    {
        sendAdminEmail("SQL update error: " + exception1.Message);
    }
    finally
    {
        con.Close();
    }

I assume the InvalidCastException is for the SqlDbType.Bit having a problem with the user.IsActive which is Boolean. When I take that parameter out of the query I still get 0 records updated. I'm beginning to wonder if this code ever worked.

I have tried converting the boolean User.IsActive to an int.

int active;

if (user.IsActive == true)
{
    active = 1;
} else
{
    active = 0;
}

command.Parameters["@Active"].Value = active;

I have tried to change the cast of the @Active parameter to <UInt32>. I have tried changing the parameter to a varchar and the bool to a string "true". I have tried writing everything into the stringbuilder with lots of quotes.

Any suggestions or documentation would help. Thank you in advance.

Upvotes: 1

Views: 280

Answers (2)

Mary Kolbadi
Mary Kolbadi

Reputation: 24

if "Active" is bit in sql and boolean in c# use:

sqlCommand.Parameters.Add("@Active", SqlDbType.Bit).Value = user.IsActive;

Upvotes: -1

afandre
afandre

Reputation: 11

While the code produces an error, it completes as expected.

The 'InvalidCastException'error is not a dealbreaker, I believe while the param may mismatch, it is included anyway.

Upvotes: -2

Related Questions