commandtext parameter changes type when executing

Question

I have a problem in my c# application, when executing a commandtext on an ms-sql-server. Here is the function i use:

public T ExecuteScalar<T>(string commandText, IDbDataParameter[] parameters)
{
    T result_value = default(T);

    using (var connection = this.CreateConnection())
    {
        connection.Open();
        using (var transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;

                command.CommandText = commandText;

                command.CommandTimeout = this.CommandTimeOut;

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                object result = command.ExecuteScalar(); //Error is thrown here!

                if (result is DBNull || result == null)
                {
                    result_value = default(T);
                }
                else
                {
                    Type type = Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T);

                    if (type.IsEnum)
                    {
                        if (Enum.IsDefined(type, Int32.Parse(result.ToString())))
                        {
                            result_value =(T)Enum.Parse(type, result.ToString());
                        }
                    }
                    else
                    {
                        result_value = (T)Convert.ChangeType(result, type);
                    }
                }
            }
            transaction.Commit();
        }
    }

    return result_value;
}

The commandtext looks like this:

"insert into tbl_Person (Vorname,Nachname,Strasse,HausNr,PLZ,Ort) output inserted.id values (@Vorname,@Nachname,@Strasse,@HausNr,@PLZ,@Ort)"

Inspecting the parameters-property of my commandtext-object states out, that the HausNr-parameter has the following values:

• db-type: string
• sql-db-type: NVarChar
• sql-value: {13/5}
• value: "13/5"

So the HausNr-parameter should be a string right? But when executing the command, the following error is thrown:

"Fehler beim Konvertieren des nvarchar-Werts \"15/3\" in den int-Datentyp."

Failed to convert the nvarchar-value \"15/3\" to the int-datatype

This looks like he is trying to convert the HausNr-parameter-value to an integer-value. Why does this happen?

Answer 1

The error message says that the database driver tries to convert yout string value to an integer value. The reason is that the column HausNr is an integer in your database.

So double check your database schema and change HausNr to an NVarChar.