Insert data to sql server from Textbox which is in a loop

Getting data from SQL Table

using (var con = new SqlConnection(constr))
{
string query = "select * from higher_office";
using (var cmd = new SqlCommand(query, con))
{
using (var sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (TableData)
{
TableData.Clear();
sda.Fill(TableData);
}
}
}
}

aspx fetched data and new Textbox for inputing values corresponding to higher office

<% for (var data = 0; data < TableData.Rows.Count; data++)
{
%>
<%=TableData.Rows[data]["higher_office_ID"]%>
<%=TableData.Rows[data]["higher_office_Name"]%>
"TextBox1" runat="server" width="150px" >
<% } %>

trying to insert as follows but not excute

using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
//SqlCommand cm = new SqlCommand("insert into item_distribution (sector,quantity) values('" + id + "','" + amount+ "')", con);
for (int i = 1; i <= rowCount; i++)
{
SqlCommand cm = new SqlCommand("insert into item_distribution (sector,quantity) values('"+ id +"','"+ amount+"')",con);
try
{
cm.ExecuteNonQuery();
RoleMessage.Text += "Successfully inserted";
RoleMessageDIV.Style["display"] = "block";
}
catch(Exception ex )
{
RoleMessage.Text +="Not inserted "+ex;
RoleMessageDIV.Style["display"] = "block";
}
}
con.Close();
}

You should never append the values that are coming from the user input such as TextBox in your SQL query as it can leads you to SQL Injection attack. https://www.c-sharpcorner.com/article/protect-your-data-prevent-sql-injection/

You should use parameterize query to prevent that. For example:

private void InsertRecords(List params){
const string sqlStatement = "INSERT INTO item_distribution (sector,quantity) VALUES (@param1,@param2)";
foreach (string p in params){
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(sqlStatement ,connection)){
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@param1", p.Id)
cmd.Parameters.AddWithValue("@param2", p.Quantity)
cmd.ExecuteNonQuery();
}
}
}
}
public class SqlParams {
public int Id {get; set;}
public string Quantity {get; set;}
}
protected void Button1_Click(object sender, EventArgs e)
{
List params = new List();
//modify the for loop here based on your requirements
foreach (int i =0; i < 10; i++)
{
//modify the assignment of values here
params.Add(new SqlParams{ Id = i; Quatity = i});
}
InsertRecords(params);
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vincent Maverick Durano6y ago
You should never append the values that are coming from the user input such as TextBox in your SQL query as it can leads you to SQL Injection attack. https://www.c-sharpcorner.com/article/protect-your-data-prevent-sql-injection/

You should use parameterize query to prevent that. For example:
private void InsertRecords(List params){
    const string sqlStatement = "INSERT INTO item_distribution (sector,quantity) VALUES (@param1,@param2)";

    foreach (string p in params){
            using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
                using(SqlCommand cmd = new SqlCommand(sqlStatement ,connection)){
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@param1", p.Id)
cmd.Parameters.AddWithValue("@param2", p.Quantity)
                        cmd.ExecuteNonQuery();
                }
        }
        }
}

public class SqlParams {
    public int Id {get; set;}
        public string Quantity {get; set;}
}


protected void Button1_Click(object sender, EventArgs e)
{
List params = new List();
    //modify the for loop here based on your requirements
    foreach (int i =0; i < 10; i++)
    {
        //modify the assignment of values here
        params.Add(new SqlParams{ Id = i; Quatity = i});
    }

       InsertRecords(params);
}