in operator in sql from asp.net c# for update multiple Record

Question

    <asp:DropDownList ID="DDLStatusChange" runat="server" AppendDataBoundItems="true">
    <asp:ListItem>-Select-</asp:ListItem>
    <asp:ListItem>Just Received</asp:ListItem>
    <asp:ListItem>Post Approval</asp:ListItem>
    </asp:DropDownList>
    <asp:TextBox ID="txtStatusChange" runat="server" Width="200"></asp:TextBox>
    <asp:Button ID="btnStatuschange"
        runat="server" Text="Status Change" onclick="btnStatuschange_Click" />

Code Behind C#

protected void btnStatuschange_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OptimaWebCustomerQueryCon"].ConnectionString))
        {
            conn.Open();
            string str = @"Update TblMasterinfo SET AppLoanStatus=@AppLoanStatus Where appid in (@Statuschange)";
            using (SqlCommand com = new SqlCommand(str, conn))
            {
                com.Parameters.AddWithValue("@Statuschange", Convert.ToInt32(txtStatusChange.Text));
                com.Parameters.AddWithValue("@AppLoanStatus",DDLStatusChange.SelectedValue);
                com.ExecuteNonQuery();
            }

            conn.Close();
        }
    }

Problem:

I input into txtStatusChange these appid for update record: 17110,17147,17524,17736,15906,17268,16440,17241 mention that when I update single recored like 17110, that update successfully. error is occured when more than one recored are updated

Exception Details: System.FormatException: Input string was not in a correct format.

Can you please try com.Parameters.AddWithValue("@Statuschange", Convert.ToString(txtStatusChange.Text));? — Deepak.Aggrawal
– Deepak.Aggrawal, Commented Jun 10, 2013 at 5:40
the problem is when you try to update more than one record, try to split the record into a list. — Sabilv
– Sabilv, Commented Jun 10, 2013 at 5:50

Jürgen Steinblock · Accepted Answer · 2013-06-10 06:45:19Z

2

Try creating a List from your input

var ids = (from x in String.Split(',', txtStatusChange.Text)
           select int.Parse(x.Trim())).ToList();

com.Parameters.AddWithValue("@Statuschange", ids);

Update: To avoid using a List parameter (which might not work for sql sever) you could use this approach:

var ids = (from x in String.Split(',', txtStatusChange.Text)
           select int.Parse(x.Trim())).ToList();    
var idString = String.Join(',', ids);

var str = String.Format(
    @"Update TblMasterinfo SET ... Where appid IN ({0})", idString);

Since you are casting your string into a List and creating a new String this is save from SQL injection so I woudn't mind not using a parameter here.

edited Jun 10, 2013 at 6:45

answered Jun 10, 2013 at 5:45

Jürgen Steinblock

31.8k26 gold badges128 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damith Over a year ago

This is not posible, check this stackoverflow.com/questions/337704/…

Jürgen Steinblock Over a year ago

I don't see why this shouldn't work (with mysql I am pretty sure that it would work)

Community · Accepted Answer · 2017-05-23 12:28:58Z

0

You can't use normal SQL parameters for IN clause but you can use table valued parameters. check this answer. but it is bit complex

You can create sql string without parameter as below. That will be easy way but make sure that your input properly validated

string input ="17110,17147,17524,17736,15906,17268,16440,17241";
string str = string.Format("Update TblMasterinfo SET AppLoanStatus=@AppLoanStatus Where appid in ({0})",input);
using (SqlCommand com = new SqlCommand(str, conn))
{
    com.Parameters.AddWithValue("@Statuschange", Convert.ToInt32(txtStatusChange.Text));
    com.ExecuteNonQuery();
}

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Jun 10, 2013 at 5:52

Damith

63.2k13 gold badges106 silver badges154 bronze badges

2 Comments

Nur Muhammad Over a year ago

I just have update: string str = @"Update TblMasterinfo SET AppLoanStatus=@AppLoanStatus Where appid in (" + txtStatusChange.Text + ")";

Nur Muhammad Over a year ago

Thanks Everyone for support me.

Collectives™ on Stack Overflow

in operator in sql from asp.net c# for update multiple Record

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related