Conversion failed when converting the nvarchar value .. to data type int

Question

I'm trying to execute the following query but getting an exception.

using (SqlConnection con = new SqlConnection(UserDatabase.getConnectionString()))
{
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM Order_Header where Status IN (@Values)"))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            try
            {
                        cmd.Connection = con;
                        con.Open();
                        sda.SelectCommand = cmd;

                        // This is for test purposes
                        List<int> yourValues = new List<int>() { 1, 2, 3, 4, 5 };

                        //Get values for IN
                        string x = String.Join(",", yourValues.Select(s => String.Format("'{0}'", s)).ToArray());

                        // Add parameter
                        cmd.Parameters.AddWithValue("@Values", x);


                        DataTable dt = new DataTable();

                        sda.Fill(dt);
                        order_details.SetDataSource(dt);
                        SalesReport.ReportSource = order_details;
            }
            catch (Exception ex)
            {
                 scriptMessage(ex.ToString);
            }
            finally
            {
                        con.Close();
                        sda.Dispose();
                        con.Dispose();
            }
        }
    }
}

On executing this query, I get the following exception:

Conversion failed when converting the nvarchar value ''1','2','3','4','5'' to data type int.

Why is this happening? Help please

Answer 1

Updated

I have changed your code as bellow and now it is working fine:

Method 1:

using (SqlConnection con =new SqlConnection(/*Your Connection String*/))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    try
                    {
                        cmd.Connection = con;
                        con.Open();
                        sda.SelectCommand = cmd;

                        // This is for test purposes
                        List<int> yourValues = new List<int>() { 1, 2, 3, 4, 5 };

                        //Get values for IN
                        string x = string.Join(",", yourValues.Select(s => String.Format("{0}", s)).ToArray());

                        cmd.CommandText = "SELECT* FROM Order_Header where Status IN(" + x + ")";

                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                    }
                    catch
                        (Exception
                            ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        con.Close();
                        sda.Dispose();
                        con.Dispose();
                    }
                }
            }

Method 2:

However if you want to use your previous code, you can achieve that by using a sql function for splitting the string. linke below:

Create function [dbo].[Split]
(
    @str nvarchar(4000), 
    @separator char(1)
)
returns table
AS
return (
    with tokens(p, a, b) AS (
        select 1, 1, charindex(@separator, @str)
        union all
        select p + 1, b + 1, charindex(@separator, @str, b + 1)
               from tokens
               where b > 0
    )
    select p-1 RowIndex,substring(@str, a, case when b > 0 then b-a ELSE 4000 end) AS s
           from tokens
  )

Now you can write your code as below:

using (SqlConnection con =new SqlConnection(/*Your Connection String*/))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Order_Header where Status IN (Select RowIndex from Split(@Values,','))"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    try
                    {
                        cmd.Connection = con;
                        con.Open();
                        sda.SelectCommand = cmd;

                        // This is for test purposes
                        List<int> yourValues = new List<int>() { 1, 2, 3, 4, 5 };

                        //Get values for IN
                        string x = String.Join(",", yourValues.Select(s => String.Format("{0}", s)).ToArray());

                        // Add parameter
                        cmd.Parameters.AddWithValue("@Values", x);

                        DataTable dt = new DataTable();

                        sda.Fill(dt);
                        //order_details.SetDataSource(dt);
                        //SalesReport.ReportSource = order_details;
                    }
                    catch
                        (Exception
                            ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        con.Close();
                        sda.Dispose();
                        con.Dispose();
                    }
                }
            }

Now this is working fine too.

Answer 2

You Status is varchar and your list is int. you need to cast you status to int if it conatins data like below:- select cast(status as int) from (select '1' as status)x;