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!

Solution

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);
    ...
}

Comments

[Paks]

I tried your Solution. But no iam getting another Error: System.FormatException: Input string was not in a correct format.

[Thorsten Dittmar]

I've made an edit to my answer to show how to make parameters optional.

[Dushan Perera]

@Paks: That means you are passing a non stringble format to the Convert.Int32 function. So i guess the values in your variables are NULL. Use visual studio breakpoitns to see which variable value is null

[Paks]

Thank you very much for your help. I appreciate that :). It works like charm now. But can you explain what the "else" is doing, i dont get it actually

[Thorsten Dittmar]

The else adds a parameter of the given name to the query, but assigns to it the value null of the SQL server (actually null in C# and null in SQL Server are different). This is to make sure that the parameter is provided to the query, but is null. While for calls to stored procedures the code would also work without the else branches if the parameters were declared to have the default value null in SQL, I'm not sure whether it'd work without the else branch in your case. You might get an error saying the parameter X was expected but not provided.

[Paks]

ah ok, in unterstand. Thanks for help again!

[Thorsten Dittmar]

Pleasure - just let me add that I'd really recommend you change the ID properties from string to int?! If you can't pass int? to the web method for whatever reason, at least declare local int? variables within the method and cast the parameters like int? localRaumID = String.IsNullOrWhitespace(RAUM_ID) ? (int?)null : Convert.ToInt32(RAUM_ID);?