Getting Error: "Error converting data type nvarchar to numeric" in SQL

Question

I am passing 4 Parameters to an asp.net Webservice. This is my Code so far:

Webmethod:

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    [WebMethod]
    public List<RaumHelper.RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
    {
        return RaumHelper.Raum(RAUMKLASSE_ID, STADT_ID, GEBAEUDE_ID, REGION_ID);
    }

Helperclass:

   public class RaumHelper
            {
                public class RAUM
                {
                    public string RaumName { get; set; }
                    public string RaumID { get; set; }

                }

                internal static List<RAUM> Raum( string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
                {
                    List<RAUM> strasseObject = new List<RAUM>();

                    using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
                    using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = @Raumklasse_ID OR STADT_ID = @Stadt_ID OR GEBAEUDE_ID = @Gebaeude_ID OR REGION_ID = @Region_ID", con)) 
                    {


                        con.Open();
                        cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
                        cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
                        cmd.Parameters.AddWithValue("@Gebaeude_ID", GEBAEUDE_ID);
                        cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);



                        using (SqlDataReader rdr = cmd.ExecuteReader())
                        {



                            while (rdr.Read())
                            {



                                if (rdr["BEZEICHNUNG"] != DBNull.Value && rdr["ID"] != DBNull.Value)
                                {

                                    strasseObject.Add(new RAUM()
                                    {
                                        RaumName = rdr["BEZEICHNUNG"].ToString(),
                                        RaumID = rdr["ID"].ToString()

                                    });
                                }

                            }
                        }
                    }
                    return strasseObject;
                }

            }

If i invoke that Webmethod with the 4 Parameters, the Method is working fine an i get a LIST of the RaumName's and RaumID's. But if i put only one Parameter iam getting an Error:

    System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()

The ID's in the Database are stored as Numeric and i passing string. I think that is the problem. But i dont know how to fix this.

I also want that my Query works also with only two or three entered Parameters.

Thank in advance!

Answer 1

Usually it's not a problem to pass a string to a parameter that's numeric, as long as the SQL Server is able to convert the content of the string to a numeric value itself. If that doesn't work, you get this error.

For example: Passing "Hello" to a parameter that's numeric, you get an error. Passing "1234" you don't. Please note that an empty string or a string containing whitespace can not be converted to a numeric value!

However, it should be said that it is not good style to do that. You should make sure that the types you use in your application match the types in the database to avoid problems. Maybe some further detail on why you need to have string types in your application could help.

EDIT 1
To make a parameter optional for the query, the way to go would be the following:

1. Change your SQL statement to allow optional parameters like WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID).
2. Do not add the @Raumklasse_ID parameter if it should be optional or add the value DBNull.Value

You should really consider changing your string properties to nullable types like int?.

EDIT 2
This is how your code could look implementing the changes I suggested in Edit 1:

using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) OR STADT_ID = ISNULL(@Stadt_ID, STADT_ID) OR GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID) OR REGION_ID = ISNULL(@Region_ID, REGION_ID)", con)) 
{
    con.Open();
    if (!String.IsNullOrWhitespace(RAUMKLASSE_ID))
        cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
    else
        cmd.Parameters.AddWithValue("@Raumklasse_ID", DBNull.Value);
    if (!String.IsNullOrWhitespace(STADT_ID))
        cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
    else
        cmd.Parameters.AddWithValue("@Stadt_ID", DBNull.Value);
    if (!String.IsNullOrWhitespace(GEBAEUDE_ID))
        cmd.Parameters.AddWithValue("@Gebaeude_ID", GEBAEUDE_ID);
    else
        cmd.Parameters.AddWithValue("@Gebaeude_ID", DBNull.Value);
    if (!String.IsNullOrWhitespace(REGION_ID))
        cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);
    else
        cmd.Parameters.AddWithValue("@Region_ID", DBNull.Value);
    ...
}