Conversion failed when converting the varchar value ''12345 '' to data type int

Question

0.00/5 (No votes)

See more:

i try to use this code to delete records from my database bout i have this error.
my column type for id is (int) where is the problem

C#

List<string> selectedIds = new List<string>();
            foreach (DataGridViewRow item in advancedDataGridView1.Rows)
            {
                if (bool.Parse(item.Cells[0].Value.ToString()))
                {
                    selectedIds.Add("'" + item.Cells[1].Value.ToString() + "'");
                    // collecting all ids
                }
            }
            String sql = "delete from tabl where id in(@idsToDelete)";
            using (SqlConnection cn = new SqlConnection("Data Source=DESKTOP-J7D5POF;Initial Catalog=ilswork;Persist Security Info=True;User ID=***;Password=***"))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.Add("@idsToDelete", SqlDbType.varchar).Value = string.Join(",", selectedIds);
                    cmd.ExecuteNonQuery();
                }
            }

What I have tried:

CSS

using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.Add("@idsToDelete", SqlDbType.Int).Value = string.Join(",", selectedIds);
                    cmd.ExecuteNonQuery();
                }

i get this error (Failed to convert parameter value from a String to a Int32)

Posted 12-Jan-19 19:04pm

el_tot93

Updated 14-Jan-19 1:30am

v2

Add a Solution

Comments

Bryian Tan 13-Jan-19 1:44am

Look like the code is passing in string instead on int

el_tot93 13-Jan-19 1:58am

so what should i do

3 solutions

Solution 3

If i understand you well, you want to pass several parameters into sql command as a single parameter (a list of id's).

C#

List<string> selectedIds = new List<string>();
//...
selectedIds.Add("'" + item.Cells[1].Value.ToString() + "'"); 
//...
String sql = "delete from tabl where id in(@idsToDelete)";
//...
cmd.Parameters.Add("@idsToDelete", SqlDbType.varchar).Value = string.Join(",", selectedIds);

As OriginalGriff mentioned, you're trying to pass string value ('1,2,3,4') into sql command instead of their original values(array of [1,2,3,4]). So, your command is getting single varchar parameter instead of an array of numeric values! This is a main cause of your error. So, you can resolve your issue by:
1) passing every single value into command and execute a command in a loop, but it may be the reason of slow performance. It might looks like dDOS attack[^]!
or
2) passing an array of values into command.
A recommended way is to use Table-Valued Parameters | Microsoft Docs[^]. Follow the link. There you'll find information how to achieve that!

Posted 14-Jan-19 1:30am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2019-01-12T20:36:00

Solution 2

Perhaps a simple way could be to concatenate an individual parameter for each value.

Consider the following example

C#

String sql;
int parameterCounter;
SqlParameter parameter;

sql = "delete from tabl where id in (";
parameterCounter = 0;

using (SqlConnection cn = new SqlConnection("....")) {
   using (SqlCommand cmd = new SqlCommand(sql, cn)) {
      foreach (DataGridViewRow item in advancedDataGridView1.Rows) {
         if (bool.Parse(item.Cells[0].Value.ToString())) {
            parameterCounter++;
            parameter = new SqlParameter();
            parameter.ParameterName = "@par" + parameterCounter.ToString();
            parameter.DbType = System.Data.DbType.Int32;
            parameter.Value = item.Cells[1].Value;
            cmd.Parameters.Add(parameter);
            sql = sql + $"{parameter.ParameterName},";
            // collecting all ids
         }
      }
      sql = sql.TrimEnd(',');
      sql = sql + ")";

      cmd.CommandText = sql;
      cmd.Connection = cn;
      cn.Open();
      cmd.ExecuteNonQuery();
   }
}

Posted 12-Jan-19 20:36pm

Wendelius

Updated 12-Jan-19 22:01pm

v5

Comments

el_tot93 13-Jan-19 3:24am

it gave me error System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

Wendelius 13-Jan-19 3:29am

If you use the debugger, what does the sql string look like?

Wendelius 13-Jan-19 3:31am

Actually I noticed the problem, The TrimEnd was missing assignment. The example has now been fixed:

sql = sql.TrimEnd(',');

el_tot93 13-Jan-19 3:55am

now debugger (The parameterized query '(@par1 int)delete from tabl where id in (@par1)' expects the parameter '@par1', which was not supplied.')

Wendelius 13-Jan-19 4:00am

Sorry, my bad. The value assigment was missing. I don't have a compiler at hand so it's a bit difficult to use just notepad...

In the example the following row has been added

parameter.Value = item.Cells[1].Value;

If you encounter more problems with my code example, let me know.

el_tot93 13-Jan-19 4:29am

Thank you very much I work with me well

el_tot93 13-Jan-19 5:04am

did we need to close the connection ???
cn.Open();
cmd.ExecuteNonQuery();
cn.close();

Wendelius 13-Jan-19 5:18am

Glad to hear it works!

Because the SqlConnection is in using block, you don't need to close the connection. Have a look at SqlConnection.Close Method (System.Data.SqlClient) | Microsoft Docs[^]

el_tot93 13-Jan-19 5:22am

thx wandelius

Wendelius 13-Jan-19 5:33am

You're welcome :)

OriginalGriff · Accepted Answer · 2019-01-12T19:43:00

Why are you trying to pass a collection of "integer" values (when in fact they are integers as strings) formed as a comma separated list of values as a single integer?

cmd.Parameters.Add("@idsToDelete", SqlDbType.Int).Value = string.Join(",", selectedIds);

Of course that isn't going to work! SQL will receive a string like this:

'1,2,3,4'

And try to convert that to a single integer value, fail, and throw an error.

Then when you fix that, there is the other problem: that won't work.
For a solution, see here: Using comma separated value parameter strings in SQL IN clauses[^]