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

`String.Format("{0}", s)`. Anyway you should pass multiple parameter as **TVP** and don't build custom sql — Lukasz Szozda, Nov 02 '15 at 09:05
You can't do `IN (@parameter)` unless @parameter is a table-valued parameter. You're passing 1 value, a string, containing the single string value `"'1', '2', '3', '4', '5'"`. This won't work. — Lasse V. Karlsen, Nov 02 '15 at 09:08
Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Peter, Nov 02 '15 at 09:16
Poor duplicate as it will generate a tablescan. There are better ways, using a table-valued parameter, or generating the correct SQL. I cannot find a good duplicate to link to right now though, someone else might have better luck. — Lasse V. Karlsen, Nov 02 '15 at 09:21

anwaar_hell · Answer 1 · 2015-11-02T09:11:02.237

0

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;

edited Nov 02 '15 at 09:11

answered Nov 02 '15 at 09:08

anwaar_hell

756
5
23

Status is int and I tried changing the following statement: string x = String.Join(",", yourValues.Select(s => String.Format("{0}", s)).ToArray()); – MAK Nov 02 '15 at 09:10
@user1377504 Or try with CAST(@Values AS int) – anwaar_hell Nov 02 '15 at 09:16
This is a string of values, not a single value. I'm not sure casting as INT would work – MAK Nov 02 '15 at 09:18
@user1377504 But you can try for that ideally the query sould be formed as SELECT * FROM Order_Header where Status IN ('1','2','3','4','5') but status is int so you need to convert these list of numbers to int to make your query to work. – anwaar_hell Nov 02 '15 at 09:22

Vahid Farahmandian · Answer 2 · 2015-11-02T09:54:04.120

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.

I've tried that but it gives me the same exception. – MAK Nov 02 '15 at 09:25 — MAK, Nov 02 '15 at 09:25

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

2 Answers2