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

enter image description here

I Use this answer http://stackoverflow.com/a/11959981/4513879 but in this field name gives error `DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Attendance'.` — Pradnya Bolli, May 11 '15 at 05:48

score 1 · Accepted Answer · answered May 11 '15 at 06:48

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.

score 0 · Answer 2 · answered May 11 '15 at 05:56

0

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;

answered May 11 '15 at 05:56

user2695786

30
9

Its give `Object reference not set to an instance of an object.` Null reference error – Pradnya Bolli May 11 '15 at 06:07
Can you tell me in which line it shows null reference error – user2695786 May 11 '15 at 06:37
I think you should check variable declaration part – user2695786 May 11 '15 at 07:02

How to save gridview each row value in table?

2 Answers2