CODEWITHSUNDEEP
c#sql
Mishigen
Mishigen

Reputation: 1261

update problem in sql

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

Upvotes: 1

Views: 302

Answers (5)

Mereghost
Mereghost

Reputation: 688

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

Like in one record with all values, or different 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();

Upvotes: 0

JonH
JonH

Reputation: 33183

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.

Upvotes: 0

regex
regex

Reputation: 3601

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.

Upvotes: 1

nos
nos

Reputation: 229344

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();

Upvotes: 3

marc_s
marc_s

Reputation: 755371

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.

Upvotes: 1

Related Questions