通过单击按钮将数据从 GridView 插入数据库

Question

我正在尝试通过单击按钮将 GridView 中的每一行插入到表中。 我对下面代码的问题是它只插入第一行并在网格视图的其余部分重复相同的记录。 例如，如果我在第一行有：

2    ABC    ATL

它将对其余列重复此操作。 如何捕获每一行并将其插入到数据库中？ 谢谢。这是我的代码：

protected void Insert(object sender, EventArgs e)
{
    foreach (GridViewRow row in GridView1.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
            Label ID = GridView1.Rows[0].FindControl("lblID") as Label;
            Label NAME = GridView1.Rows[0].FindControl("lblQue") as Label;
            TextBox LOC = GridView1.Rows[0].FindControl("txtPM") as TextBox;
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText =
                "insert into myTable(ID, NAME, LOC ) values(@ID, @NAME,  @LOC) " +
                // "where ID=@ID;" + 
                "SELECT ID, NAME, LOC FROM MYTABLE";
            cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
            cmd.Parameters.Add("@NAME", SqlDbType.VarChar).Value = NAME.Text;
            cmd.Parameters.Add("@LOC", SqlDbType.VarChar).Value = LOC.Text;
            GridView1.EditIndex = -1;
            GridView1.DataSource = GetData(cmd);
            GridView1.DataBind();
        }
    }
}

Answer 1

问题：您使用以下语句硬编码索引零：

GridView1.Rows[0]

它将始终插入GridView的第一条记录

解决方案：增加每行的行数。

试试这个：

 protected void Insert(object sender, EventArgs e)
 {  
   int index=0;
   foreach (GridViewRow row in GridView1.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
                  Label ID = GridView1.Rows[index].FindControl("lblID") as Label;
                  Label NAME = GridView1.Rows[index].FindControl("lblQue") as Label;
                  TextBox LOC = GridView1.Rows[index].FindControl("txtPM") as TextBox;

                SqlConnection con = new SqlConnection(strConnString);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into myTable(ID, NAME, LOC ) values(@ID, @NAME,  @LOC) " +
                    // "where ID=@ID;" + 
                 "SELECT ID, NAME, LOC FROM MYTABLE";
                cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                cmd.Parameters.Add("@NAME", SqlDbType.VarChar).Value = NAME.Text;
                cmd.Parameters.Add("@LOC", SqlDbType.VarChar).Value = LOC.Text;
                GridView1.EditIndex = -1;
                GridView1.DataSource = GetData(cmd);
                GridView1.DataBind();

        }
         index++;
    }
  }

Answer 2

GridView.Rows[0]将占据所有行的第一行。

而不是使用foreach声明中的行。

取代

 Label ID = GridView1.Rows[0].FindControl("lblID") as Label;

跟

 Label ID = row.FindControl("lblID") as Label;

Answer 3

请使用此代码

row.FindControl("lblID") as Label;而不是GridView1.Rows[0].FindControl("lblID") as Label;

并首先执行插入查询，然后调用选择查询

 SqlConnection con = new SqlConnection(strConnString);
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "insert into myTable(ID, NAME, LOC ) values(@ID, @NAME,  @LOC) ";
                    cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
                    cmd.Parameters.Add("@NAME", SqlDbType.VarChar).Value = NAME.Text;
                    cmd.Parameters.Add("@LOC", SqlDbType.VarChar).Value = LOC.Text;
cmd.ExcutrNonQuery();
cmd.CommandText = "SELECT ID, NAME, LOC FROM MYTABLE";
.... //next binding  code