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, Jun 10 '13 at 05:40
the problem is when you try to update more than one record, try to split the record into a list. — Sabilv, Jun 10 '13 at 05:50

Jürgen Steinblock · Answer 1 · 2013-06-10T06:45:19.110

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 '13 at 06:45

answered Jun 10 '13 at 05:45

Jürgen Steinblock

30,746
24
119
189

This is not posible, check this http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause – Damith Jun 10 '13 at 06:03
I don't see why this shouldn't work (with mysql I am pretty sure that it would work) – Jürgen Steinblock Jun 10 '13 at 06:38

score 0 · Answer 2 · edited May 23 '17 at 12:28

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 '17 at 12:28

Community

1
1

answered Jun 10 '13 at 05:52

Damith

62,401
13
102
153

I just have update: string str = @"Update TblMasterinfo SET AppLoanStatus=@AppLoanStatus Where appid in (" + txtStatusChange.Text + ")"; – Nur Muhammad Jun 10 '13 at 10:54
Thanks Everyone for support me. – Nur Muhammad Jun 10 '13 at 10:55

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

2 Answers2