Insert multiple ASP.Net GridView rows to database using for loop in C# and VB.Net

Hi rajeesh,

Check this sample. now take its reference and correct your code.

HTML

<asp:GridView ID="gvItems" runat="server" AutoGenerateColumns="False" ShowFooter="True">
    <Columns>
        <asp:BoundField DataField="Category" HeaderText="Category" />
        <asp:BoundField DataField="ItemName" HeaderText="Item Name" />
        <asp:TemplateField HeaderText="Image">
            <ItemTemplate>
                <asp:Image ID="imgFood" runat="server" Height="50px" ImageUrl='<%# Eval("Image") %>'
                    Width="50px" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Price" HeaderText="Price" />
        <asp:TemplateField HeaderText="Size">
            <FooterTemplate>
                <asp:Button ID="btnCheckOut" runat="server" Text="Checkout" />
                <asp:Button ID="btnCalculate" runat="server" Text="Calculate" />
            </FooterTemplate>
            <ItemTemplate>
                <asp:DropDownList ID="ddlSizeList" runat="server">
                    <asp:ListItem Text="Select" Value="Select"></asp:ListItem>
                    <asp:ListItem Text="1" Value="1"></asp:ListItem>
                    <asp:ListItem Text="2" Value="2"></asp:ListItem>
                    <asp:ListItem Text="3" Value="3"></asp:ListItem>
                </asp:DropDownList>
                <br />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Quantity">
            <FooterTemplate>
                <asp:Label ID="lblSubTotal" runat="server" Text="Sub Total"></asp:Label>
            </FooterTemplate>
            <ItemTemplate>
                <asp:TextBox ID="txtQuantity" runat="server" Text='<%# Eval("Quantity") %>' Height="30px"
                    Width="40px"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Total">
            <ItemTemplate>
                <asp:Label ID="lblTotal" runat="server" Text='<%# Eval("Total") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:Label ID="lblGrandTotal" runat="server" Text='<%# Eval("Total") %>'></asp:Label>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:CommandField ShowSelectButton="True" />
    </Columns>
</asp:GridView>
<asp:Button ID="btnSubmit" Text="Submit" runat="server" OnClick="OnSubmit" />

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        Image img = new Image();
        DataTable dt = new DataTable();
        dt.Columns.Add("Category");
        dt.Columns.Add("ItemName");
        dt.Columns.Add("Image");
        dt.Columns.Add("Price");
        dt.Columns.Add("Size");
        dt.Columns.Add("Quantity");
        dt.Columns.Add("Total");
        dt.Rows.Add("Food", "Apple", "Images/Hydrangeas.jpg", 100, "", 1, 100);
        dt.Rows.Add("Food", "Orange", "Images/Koala.jpg", 50, "", 2, 100);
        dt.Rows.Add("Food", "Mango", "Images/Lighthouse.jpg", 150, "", 1, 150);
        dt.Rows.Add("Stationery", "Books", "Images/Penguins.jpg", 200, "", 1, 200);
        dt.Rows.Add("Vegitable", "Tomato", "Images/Tulips.jpg", 20, "", 1, 20);
        this.gvItems.DataSource = dt;
        this.gvItems.DataBind();
    }
}

protected void OnSubmit(object sender, EventArgs e)
{
    foreach (GridViewRow row in gvItems.Rows)
    {
        string ddlSize = Convert.ToString((row.FindControl("ddlSizeList") as DropDownList).SelectedValue);
        if (ddlSize == "Select")
        {
            Response.Write("<script>alert('Kindly Choose Option')</script>");
            break;
        }
        else
        {
            Image imgfood = (row.FindControl("imgFood") as Image);
            string category = row.Cells[0].Text;
            string itemName = row.Cells[1].Text;
            string imageUrl = imgfood.ImageUrl;
            int price = Convert.ToInt32(row.Cells[3].Text);
            string size = Convert.ToString((row.FindControl("ddlSizeList") as DropDownList).SelectedValue);
            int quantity = Convert.ToInt32((row.FindControl("txtQuantity") as TextBox).Text);
            int total = Convert.ToInt32((row.FindControl("lblTotal") as Label).Text);
            this.Insert(category, itemName, imageUrl, price, size, quantity, total);
        }
    }
}

private void Insert(string category, string itemName, string imageUrl, int price, string size, int quantity, int total)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO tblItems VALUES(@category,@itemName,@ImageUrl,@price,@size,@quantity,@total)"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@category", category);
            cmd.Parameters.AddWithValue("@itemName", itemName);
            cmd.Parameters.AddWithValue("@ImageUrl", imageUrl);
            cmd.Parameters.AddWithValue("@price", price);
            cmd.Parameters.AddWithValue("@size", size);
            cmd.Parameters.AddWithValue("@quantity", quantity);
            cmd.Parameters.AddWithValue("@total", total);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim img As Image = New Image()
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("Category")
        dt.Columns.Add("ItemName")
        dt.Columns.Add("Image")
        dt.Columns.Add("Price")
        dt.Columns.Add("Size")
        dt.Columns.Add("Quantity")
        dt.Columns.Add("Total")
        dt.Rows.Add("Food", "Apple", "Images/Hydrangeas.jpg", 100, "", 1, 100)
        dt.Rows.Add("Food", "Orange", "Images/Koala.jpg", 50, "", 2, 100)
        dt.Rows.Add("Food", "Mango", "Images/Lighthouse.jpg", 150, "", 1, 150)
        dt.Rows.Add("Stationery", "Books", "Images/Penguins.jpg", 200, "", 1, 200)
        dt.Rows.Add("Vegitable", "Tomato", "Images/Tulips.jpg", 20, "", 1, 20)
        Me.gvItems.DataSource = dt
        Me.gvItems.DataBind()
    End If
End Sub

Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As GridViewRow In gvItems.Rows
        Dim ddlSize As String = Convert.ToString((TryCast(row.FindControl("ddlSizeList"), DropDownList)).SelectedValue)
        If ddlSize = "Select" Then
            Response.Write("<script>alert('Kindly Choose Option')</script>")
            Exit For
        Else
            Dim imgfood As Image = (TryCast(row.FindControl("imgFood"), Image))
            Dim category As String = row.Cells(0).Text
            Dim itemName As String = row.Cells(1).Text
            Dim imageUrl As String = imgfood.ImageUrl
            Dim price As Integer = Convert.ToInt32(row.Cells(3).Text)
            Dim size As String = Convert.ToString((TryCast(row.FindControl("ddlSizeList"), DropDownList)).SelectedValue)
            Dim quantity As Integer = Convert.ToInt32((TryCast(row.FindControl("txtQuantity"), TextBox)).Text)
            Dim total As Integer = Convert.ToInt32((TryCast(row.FindControl("lblTotal"), Label)).Text)
            Me.Insert(category, itemName, imageUrl, price, size, quantity, total)
        End If
    Next
End Sub

Private Sub Insert(ByVal category As String, ByVal itemName As String, ByVal imageUrl As String, ByVal price As Integer, ByVal size As String, ByVal quantity As Integer, ByVal total As Integer)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO tblItems VALUES(@category,@itemName,@ImageUrl,@price,@size,@quantity,@total)")
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@category", category)
            cmd.Parameters.AddWithValue("@itemName", itemName)
            cmd.Parameters.AddWithValue("@ImageUrl", imageUrl)
            cmd.Parameters.AddWithValue("@price", price)
            cmd.Parameters.AddWithValue("@size", size)
            cmd.Parameters.AddWithValue("@quantity", quantity)
            cmd.Parameters.AddWithValue("@total", total)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub