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

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!

Answers


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

The problem might be that you're not giving anything to infer the data type from as you're not setting the value of the parameter.

Try setting the parameters using the Add method as follows:

cmd.Parameters.Add("@Raumklasse_ID", SqlDbType.Int).Value = RAUMKLASSE_ID;

That way if you don't want to set the value, you can still define the parameter.

See here for more: http://msdn.microsoft.com/en-us/library/wbys3e9s


Convert your string to intger type

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

Do like this

cmd.Parameters.Add("@Raumklasse_ID", SqlDbType.Int);// use here your SQL DataType, if it is numberic than use Numeric.
cmd.Parameters[0].value = Convert.ToInt32(RAUMKLASSE_ID);

Need Your Help

SpringMVC, MySQL dynamic query

java mysql spring-mvc dynamicquery dynamic-queries

How I can make one dynamic query in Spring MVC with all my all my parameters from URL?