How to save gridview each row value in table?

Question

How to insert gridview each row value into database.I use below code but null values are pass in strings.

foreach (GridViewRow gvrow in GridView1.Rows)
            {
                con.Open();

                string datetime = Request.Form["txtdate"];
                str += GridView1.DataKeys[gvrow.RowIndex].Value.ToString() + ',';
                Id  += gvrow.Cells[0].Text;
                fName += gvrow.Cells[1].Text;
                FaName  += gvrow.Cells[2].Text;
                LName  += gvrow.Cells[3].Text;
                attendance  += gvrow.Cells[4].Text;
                remarks  += gvrow.Cells[5].Text;
                SqlCommand cmd = new SqlCommand("INSERT INTO [first].[dbo].[Staff_Attendance]([Id],[Department],[Date],[First_name],[Father_name],[Last_name],[Attendance],[Remarks]) VALUES(@Id, @Department, @Date, @First_name, @Father_name, @Last_name, @Attendance, @Remarks)", con);
                cmd.Parameters.AddWithValue("@Id", Id);
                cmd.Parameters.AddWithValue("@Department", DropDownList1.SelectedItem .ToString ());
                cmd.Parameters.AddWithValue("@Date", datetime.ToString());

                cmd.Parameters.AddWithValue("@First_name", fName);
                cmd.Parameters.AddWithValue("@Father_name", FaName);
                cmd.Parameters.AddWithValue("@Last_name", LName);
                cmd.Parameters.AddWithValue("@Attendance", attendance);
                cmd.Parameters.AddWithValue("@Remarks", remarks);

                cmd.ExecuteNonQuery();
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Data Have Been Saved')", true);
                con.Close();
            }

Gridview code:-

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" onpageindexchanging="GridView1_PageIndexChanging" 
                    PageSize="5" AutoGenerateColumns="False" DataKeyNames ="srno">
                    <PagerSettings FirstPageText="First" LastPageText="Last" 
                        Mode="NumericFirstLast" PageButtonCount="5" />
                        <Columns >
                        <asp:TemplateField HeaderText="Sr.No.">


    <ItemTemplate>
  <asp:Label ID="lblsrno" runat="server" Text='<%#Eval("srno") %>'>
    </asp:Label>

    </ItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="First Name">


    <ItemTemplate>
  <asp:Label ID="lblFname" runat="server" Text='<%#Eval("first_name") %>'>
    </asp:Label>

    </ItemTemplate>
    </asp:TemplateField>

       <asp:TemplateField HeaderText="Father Name">


    <ItemTemplate>
  <asp:Label ID="lblFaName" runat="server" Text='<%#Eval("father_name") %>'>
    </asp:Label>

    </ItemTemplate>
    </asp:TemplateField>

       <asp:TemplateField HeaderText="Last Name">


    <ItemTemplate>
  <asp:Label ID="lblLName" runat="server" Text='<%#Eval("last_name") %>'>
    </asp:Label>

    </ItemTemplate>
    </asp:TemplateField>

       <asp:TemplateField HeaderText="Attendance">


    <ItemTemplate>
 <asp:DropDownList ID="ddlDesignation" runat="server" Width ="80px">
<asp:ListItem Text ="--Select--" ></asp:ListItem>
<asp:ListItem Text ="P"></asp:ListItem>
<asp:ListItem Text ="A"></asp:ListItem>
                        </asp:DropDownList>


    </ItemTemplate>
    </asp:TemplateField>


    <asp:TemplateField HeaderText="Remarks">


    <ItemTemplate>
 <asp:DropDownList ID="ddlRemark" runat="server" Width ="80px">
<asp:ListItem Text ="--Select--" ></asp:ListItem>
<asp:ListItem Text ="Paid Leave"></asp:ListItem>
<asp:ListItem Text ="Unpaid Leave"></asp:ListItem>
                        </asp:DropDownList>


    </ItemTemplate>
    </asp:TemplateField>
                        </Columns>
                </asp:GridView>

In database it stored null values

Answer 1

You can try using following code, have written a sample code ,

foreach (GridViewRow row in GridView1.Rows) {

    Label lblFname = (Label)row.FindControl("lblFname");
    Label lblFaName = (Label)row.FindControl("lblFaName");
    Label lblLName = (Label)row.FindControl("lblLName");
    DropDownList ddl_att = (DropDownList)row.FindControl("ddlDesignation");
    DropDownList ddl_rmk = (DropDownList)row.FindControl("ddlRemark");

    dataInsert(lblFname.Text,lblFaName.Text,ddl_att.SelectedValue);

}


public void dataInsert(string First_name,string Father_name,string Attendance)
{
 using (SqlConnection con = new SqlConnection(conn.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "yourInsertQuery";
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@First_name", First_name);
            cmd.Parameters.AddWithValue("@Father_name", Father_name);
            cmd.Parameters.AddWithValue("@Attendance", Attendance);
                ...
                ...     
            con.Open();
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}

Also you can save data into datatable and if your DataTable schema and table schema are matched, then using SqlBulkCopy you can do bulk insert into your database.

Answer 2

First You can find the controls in gridview then get Text. May be your problem will solve.

            fName += ((Label)gvrow.Cells[1].FindControl("lblFname")).Text;
            FaName  +=((Label)gvrow.Cells[2].FindControl("lblFaName").Text);
            LName  += ((Label)gvrow.Cells[3].FindControl("lblLName")).Text;
            attendance  +=((DropDownList)gvrow.Cells[4].FindControl("ddlDesignation")).SelectedItem.Text;
            remarks  += ((DropDownList)gvrow.Cells[5].FindControl("ddlRemark")).SelectedItem.Text;