update problem in sql

Question

i have an update statement,which gives an error "Failed to convert parameter value from a String[] to a String."

SqlCommand comm1 = new SqlCommand("UPDATE count set ETR=@ETR WHERE Id IN ( " + itemIDs + ")", connection);
                comm1.Parameters.Add("@ETR", System.Data.SqlDbType.VarChar, 50);
                comm1.Parameters["@ETR"].Value = delivery;

where itemIDs is an array.now i want to set the value ETR equals to values retrieved from an array "delivery".i mean to say for each itemID there is a value in delivery array and this command shouls set the value of ETR for each itemID to corresponding value in delivery array

Answer 1

I'm guessing that ItemIds is a string array. You need to build a function that writes itemIDs to a comma separated string

public string ArrayToCsv(string[] arr)
{
    StringBuilder sb = new StringBuilder();
    for each(string e in arr)
    {
        sb.Append(e);
        sb.Append(',');
    }

    if (sb.Length > 0)
        sb.Remove(sb.Length - 1, 1);

    return sb.ToString();
}

I just through that code together from memory, so it might not be 100% accurate, but it should give you an idea.

Answer 2

The SQL statement is expecting a comma separated string especially with IN. Just pass the array to a function which takes the array and changes 123456 into 1,2,3,4,5,6 and passes it back.

Answer 3

You should preform 1 update command for every item in your delivery, preferrably wrapped in a transaction.

SqlTransaction tx = connection.BeginTransaction();
SqlCommand comm1 = new SqlCommand("UPDATE count set ETR=@ETR WHERE Id=@ID", connection);
comm1.Connection = connection;
comm1.Transaction = transaction;
comm1.Parameters.Add("@ID", System.Data.SqlDbType.Int);
comm1.Parameters.Add("@ETR", System.Data.SqlDbType.VarChar, 50);
for(int i = 0; i < itemIDs.Count; i++) {



    comm1.Parameters["@ID"].Value = itemIDs[i];

    comm1.Parameters["@ETR"].Value = delivery[i];
    comm1.ExecuteNonQuery();
 }
 tx.Commit();

Answer 4

comm1.Parameters.Add("@ETR", System.Data.SqlDbType.VarChar, 50); comm1.Parameters["@ETR"].Value = delivery;

Your parameter "@ETR" is a single string. The thing you're assigning to it "delivery" is an array of strings, as you say yourself. That won't work - ever.

You need to rethink your logic somehow.

Answer 5

he problem is not in itemID ,its in statement comm1.Parameters["@ETR"].Value = delivery; as ETR is a string and delivry ia an array and i want to pass each value of delivery in ETR

Like in one record with all values, or diferent records?

Using one record, you need to parse the string array to csv or the like.

StringBuilder sb = new StringBuilder();
foreach (string s in Delivery)
{
    sb.Append(s);
    sb.Append(",");
}
sb.Remove(sb.Length-2,1).ToString();