Insert new records in Database using GridView C# and Vb.Net

← PrevNext →

No need to say that an Asp.Net GridView control is one of the most versatile tools web developers used on their websites or web applications. It is easy to use and it comes with many useful features. In one of my previous articles, I have written about how to edit, update and delete records in a GridView control using Asp.Net. What I have missed is a discussion on how we can insert new records in a database table using a GridView or how to add new row in GridView control.

In addition to this, I am going to show you how to add a jQuery Datepicker control in each GridView row, in edit mode. The below images says it all. The image clearly shows the Datepicker control (widget) before the “Insert Record” button.

Insert New Record Using GridView

Insert new records using GridView

Here is a scenario. XYZ company issues books to its employees on weekends and maintains a database on issued books with the names of the Employee, the issuing date and the category of these books. It’s a simple senario and this will help us understand how Asp.Net GridView can be used to insert records, row by row.

First create a table dbo.Books in the database

I recommend using this SQL Server dummy database with tables and few dummy data in it. It will save some time.

CREATE TABLE dbo.Books
(
    BookID INT IDENTITY(1,1) PRIMARY KEY,
    BookName VARCHAR(50) NULL,
    Category VARCHAR(50) NULL,
    EmployeeName VARCHAR(100),
    DateIssued DATETIME NULL
)

I am using jQuery for two reasons.

01) To show a dropdown list of Books using jQuery AutoComplete feature. An array provides the name of the Books.

02) jQuery .datepicker() function is used to select the issuing date in one of the cells in the GridView control.

$(function() { $('input[id*=tbIssueDate]').datepicker(); }); // jQuery DATE PICKER.

Learn more about jQuery .datepicker().

The Markup with CSS
<head>
    <title>Insert New Records in Database Table Using GridView</title>
    
    <%--jQuery CDN.--%>
    <link href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>    
    
    <%--GRIDVIEW STYLE.--%>
    <style>
        body { font-size:62.5% }
        #divBooks {
            font:11px Verdana; 
            width:750px;
            overflow-y:scroll;
            display:block;
            padding:10px 0 0 0;
        }
        .textbox {
            width:70px;
            padding:2px 4px;
            font:inherit;
            text-align:left;
            border:solid 1px #BFBFBF;
            border-radius:2px; -moz-border-radius:2px; -webkit-border-radius:2px; 
            text-transform:uppercase;
        }
        .Grid {
            width:100%; 
            font:inherit; 
            margin:5px 0 10px 0; 
            background-color:#FFF; 
            border:solid 1px #525252;
        }
        .Grid td {
            font:inherit; 
            padding:2px; 
            border:solid 1px #C1C1C1; 
            color:#333; 
            text-align:center;
        }
        .Grid th {
            padding:3px; 
            color:#FFF; 
            background:#424242 
            border-left:solid 1px #525252; 
            font:inherit;
        }
        .button {
            cursor:pointer; 
            text-align:center; 
            color:#FFF; 
            font:inherit;
            background-color:#4D90FE;
            border:solid 1px #3079ED; 
            border-radius:5px; 
            -moz-border-radius:5px; 
            -webkit-border-radius:5px; 
            line-height:15px;
        }
    </style>
</head>

Add the GridView control

<body>
    <form id="frmBooks" runat="server">
    <asp:ScriptManager ID="scriptManager" runat="server" EnablePartialRendering="true" EnablePageMethods="true" />

    <div id="divBooks">
        <asp:UpdatePanel runat="server" UpdateMode="Conditional">
            <ContentTemplate>
            
                <asp:GridView ID="GridView1" 
                    AutoGenerateColumns="false" 
                    CssClass="Grid" 
                    GridLines="None" 
                    ShowFooter="true" 
                    AllowPaging="true" 
                    PageSize="5"
                    runat="server">
            
                    <Columns>
                        <asp:TemplateField HeaderText="ID">
                            <ItemTemplate><%#Eval("BookID")%> </ItemTemplate>
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Book">
                            <ItemTemplate> <%#Eval("BookName")%> </ItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="tbBook" Width="200px" CssClass="textbox" runat="server" />
                            </FooterTemplate>
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Category">
                            <ItemTemplate> <%#Eval("Category")%> </ItemTemplate>
                            <FooterTemplate><asp:TextBox ID="tbCategory" CssClass="textbox" runat="server" />
                                </FooterTemplate>
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Employee Name">
                            <ItemTemplate> <%#Eval("EmployeeName")%></ItemTemplate>
                            <FooterTemplate><asp:TextBox ID="tbEmpName" Width="140px" CssClass="textbox" 
                                runat="server"></asp:TextBox></FooterTemplate>
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Issue Date">
                            <ItemTemplate><asp:Label ID="lblIsseDate" width="140px" runat="server" Text='
                                <%#Eval("DateIssued")%>'></asp:Label></ItemTemplate>
                            <FooterTemplate><asp:TextBox ID="tbIssueDate" DataFormatString="{dd/MM/yyyy}"
                                CssClass="textbox" runat="server"></asp:TextBox></FooterTemplate>
                        </asp:TemplateField>
            
                        <asp:TemplateField>
                            <ItemTemplate></ItemTemplate>
                            <FooterTemplate>
                                <asp:Button ID="btInsert" 
                                    Text="Insert Record" CssClass="button" 
                                    OnClick="InsertRecord" 
                                    CommandName="Footer" runat="server" />
                            </FooterTemplate>
                        </asp:TemplateField>
                    </Columns>

                    <EmptyDataTemplate>  <%--SHOWS BLANK FILEDS AT THE BOTTOM OF THE GRIDVIEW.--%>
                        <tr>
                            <th>Book</th>
                            <th>Category</th>
                            <th>Employee Name</th>
                            <th>Issue Date</th>
                            <th></th>
                        </tr>
                        <tr>
                            <td><asp:TextBox ID="tbBookName" Width="200px" CssClass="textbox" runat="server" /></td>
                            <td><asp:TextBox ID="tbCategory" CssClass="textbox" runat="server" /></td>
                            <td><asp:TextBox ID="tbEmpName" Width="200px" CssClass="textbox" runat="server" /></td>
                            <td>
                                <asp:TextBox ID="tbIssueDate" 
                                    DataFormatString="{dd/MM/yyyy}" 
                                    CssClass="textbox" 
                                    runat="server" />
                            </td>
                            <td>
                                <asp:Button ID="btInsert" Text="Insert Record" 
                                    CssClass="button" OnClick="InsertRecord" 
                                    CommandName="EmptyDataTemplate" runat="server" />
                            </td>
                        </tr>
                    </EmptyDataTemplate>
                </asp:GridView>
    
            </ContentTemplate>
        </asp:UpdatePanel>
    </div>
    </form>
</body>
The Script

We will create a script to attach an AutoComplete drop down list to the first column if the GridView. The drop down list shows a list of books.

<script>
    $(document).ready(function() {
        var prm = Sys.WebForms.PageRequestManager.getInstance();
        prm.add_initializeRequest(InitializeRequest);
        prm.add_endRequest(EndRequest);

        BindControls();
    });
    function InitializeRequest(sender, args) { }
    function EndRequest(sender, args) { BindControls(); }

    function BindControls() {
        // BOOKS IN AN ARRAY.
        var books = ['The DNA of Unix Programming', 'The Hacker Crackdown',
            'History of Economic Meltdown', 'Business of Basic Economics',
            'Hospitality Accounts', 'The Alejandra Variations', 'Ancient Echoes'];

        $('input[id*=tbBookName]').autocomplete({ source: books });

        // jQuery DATE PICKER.
        $(function() { $('input[id*=tbIssueDate]').datepicker(); });
    }
</script>
Code Behind (C#)
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

partial class _Default : System.Web.UI.Page
{
    string sCon = "Data Source=dna;Persist Security Info=False;Integrated Security=SSPI;" + 
        "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGrid_With_Data();
        }
    }
    private void BindGrid_With_Data()
    {
        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
            {
                SqlDataAdapter sda = new SqlDataAdapter();
                try
                {
                    cmd.Connection = con;
                    con.Open();
                    sda.SelectCommand = cmd;

                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    // BIND DATABASE WITH THE GRIDVIEW.
                    GridView1.DataSource = dt;
                    GridView1.DataBind();

                }
                catch (Exception ex)
                { // }
            }
        }
    }
    protected void GridView1_PageIndexChanging(object sender, 
        System.Web.UI.WebControls.GridViewPageEventArgs e)
    {
        // GRIDVIEW PAGING.
        GridView1.PageIndex = e.NewPageIndex;
        BindGrid_With_Data();
    }
    protected void InsertRecord(object sender, EventArgs e)
    {
        // GET THE ACTIVE GRIDVIEW ROW.
        Button bt = (Button)sender;
        GridViewRow grdRow = (GridViewRow)bt.Parent.Parent;

        // NOW GET VALUES FROM FIELDS FROM THE ACTIVE ROW.
        TextBox tbBookName = (TextBox)grdRow.Cells[0].FindControl("tbBookName");
        TextBox tbCategory = (TextBox)grdRow.Cells[0].FindControl("tbCategory");
        TextBox tbEmpName = (TextBox)grdRow.Cells[0].FindControl("tbEmpName");
        TextBox tbDate = (TextBox)grdRow.Cells[0].FindControl("tbIssueDate");

        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
            {
                // FINALLY INSERT ROW VALUES IN THE TABLE.
                cmd.Connection = con;
                con.Open();

                cmd.CommandText = "INSERT INTO dbo.Books (BookName, Category, 
                    EmployeeName, DateIssued) " + 
                        "VALUES(@BookName, @Category, @EmployeeName, @DateIssued)";
                cmd.Parameters.AddWithValue("@BookName", tbBookName.Text.Trim());
                cmd.Parameters.AddWithValue("@Category", tbCategory.Text.Trim());
                cmd.Parameters.AddWithValue("@EmployeeName", tbEmpName.Text.Trim());
                cmd.Parameters.AddWithValue("@DateIssued", tbDate.Text.Trim() + " " + 
                    DateTime.Now.TimeOfDay);

                cmd.ExecuteNonQuery();
            }
        }

        // REFRESH THE GRIDVIEW CONTROL TO SHOW THE NEWLY INSERTED ROW.
        BindGrid_With_Data();
    }
}
Vb.Net
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Dim objConn As SqlConnection
    Dim SqlCom As SqlCommand
    Dim sCon As String = "Data Source=DNA;Persist Security Info=False;" & _
        "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;"

    Protected Sub frmBooks_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles frmBooks.Load
        If Not Page.IsPostBack Then
            BindGrid_With_Data()
        End If
    End Sub

    Private Sub BindGrid_With_Data()
        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")

                Dim sda As SqlDataAdapter = New SqlDataAdapter
                Try
                    cmd.Connection = con : con.Open()
                    sda.SelectCommand = cmd

                    Dim dt As DataTable = New DataTable
                    sda.Fill(dt)

                    ' BIND DATABASE WITH THE GRIDVIEW.
                    GridView1.DataSource = dt
                    GridView1.DataBind()

                Catch ex As Exception
                    '
                End Try
            End Using
        End Using
    End Sub

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, 
            ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) 
                Handles GridView1.PageIndexChanging

        ' GRIDVIEW PAGING.
        GridView1.PageIndex = e.NewPageIndex
        BindGrid_With_Data()
    End Sub

    ' INSERT THE RECORDS.
    Protected Sub InsertRecord(ByVal sender As Object, ByVal e As EventArgs)
        ' GET THE ACTIVE GRIDVIEW ROW.
        Dim bt As Button = DirectCast(sender, Button)
        Dim grdRow As GridViewRow = DirectCast(bt.Parent.Parent, GridViewRow)

        ' NOW GET VALUES FROM FIELDS FROM THE ACTIVE ROW.
        Dim tbBookName As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbBookName"), TextBox)
        Dim tbCategory As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbCategory"), TextBox)
        Dim tbEmpName As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbEmpName"), TextBox)
        Dim tbDate As TextBox = DirectCast(grdRow.Cells(0).FindControl("tbIssueDate"), TextBox)

        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")

                ' FINALLY INSERT ROW VALUES IN THE TABLE.
                With cmd
                    .Connection = con : con.Open()

                    .CommandText = "INSERT INTO dbo.Books ( " & _
                        "BookName, Category, EmployeeName, DateIssued) " & _
                        "VALUES(@BookName, @Category, @EmployeeName, @DateIssued)"
                    .Parameters.AddWithValue("@BookName", Trim(tbBookName.Text))
                    .Parameters.AddWithValue("@Category", Trim(tbCategory.Text))
                    .Parameters.AddWithValue("@EmployeeName", Trim(tbEmpName.Text))
                    .Parameters.AddWithValue("@DateIssued", Trim(tbDate.Text) & " " & 
                        TimeOfDay)

                    .ExecuteNonQuery()
                End With
            End Using
        End Using

        BindGrid_With_Data()   ' REFRESH THE GRIDVIEW CONTROL TO SHOW THE NEWLY INSERTED ROW.
    End Sub
End Class

← PreviousNext →