ASP. NET add data in GridView Control using VB.NET

This article describes you how to add data using an ASP.NET GridView Control in SQL Server database.
  • 8625
 


This articles describes you how to add data using an ASP.NET GridView Control in SQL Server database using Visual Basic. Firstly we make a table in SQL database and insert some data in this table.

Creating Table in SQL Server Database

Now we create a table which named as userinfo. the table looks like the below.

CREATE TABLE [dbo].[userinfo](

      [UserId] [int] IDENTITY(1,1) NOT NULL,

      [UserName] [varchar](50) NULL,

      [userstate] [varchar](50) NULL,

      [UserCity] [varchar](50) NULL

      )

      go

     

Now Insert data in the Table.

 

insert into userinfo values('Rohatash','up','mathura');

go

insert into userinfo values('monu','mp','agra');

go

insert into userinfo values('ram','delhi','Delhi');

go

select * from userinfo ;

OUTPUT 
 

tabl1.gif
 

Figure1

Now We will use SQL Client data provider to provide database connectivity.

Before you can use any classes related to SQL Client data adapter, we need to import the SqlClient namespace in your application by using the following using statement.

Imports System.Data.SqlClient

The ASP.NET code for the DataView control. In this code below code, you see database table columns binding with the bound fields and formatting is provided using the template fields.

<asp:GridView ID="ResultGridView" runat="server" AutoGenerateColumns="False"ShowFooter="True"

                AllowPaging="True" onrowcommand="ResultGridView_RowCommand"

        >

           <Columns>

               <asp:BoundField DataField="UserId" HeaderText="UserId" InsertVisible="False"

                   ReadOnly="True" SortExpression="UserId" />

               <asp:TemplateField HeaderText="UserName" SortExpression="VendorFName">

                   <EditItemTemplate>

                        <asp:TextBox ID="txtuserName" Width="100px" runat="server" Text='<%#Bind("UserName") %>'></asp:TextBox>

                   </EditItemTemplate>

                   <FooterTemplate>

                   <asp:TextBox ID="txtuserName" runat="server" Width="100px"></asp:TextBox>

                   </FooterTemplate>

                   <ItemTemplate>

                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("UserName") %>'></asp:Label>

                   </ItemTemplate>

               </asp:TemplateField>

               <asp:TemplateField HeaderText="Userstate" SortExpression="userstate">

                   <EditItemTemplate>

                        <asp:TextBox ID="txtuserstate" Width="100px" runat="server" Text='<%#Bind("Userstate") %>'></asp:TextBox>

                   </EditItemTemplate>

                   <FooterTemplate>

                   <asp:TextBox ID="txtuserstate" Width="100px" runat="server" ></asp:TextBox>

                   </FooterTemplate>

                   <ItemTemplate>

                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("UserCity") %>'></asp:Label>

                   </ItemTemplate>

               </asp:TemplateField>

               <asp:TemplateField HeaderText="UserCity" SortExpression="VendorCity">

                   <EditItemTemplate>

                        <asp:TextBox ID="txtusercity" Width="100px" runat="server" Text='<%#Bind("UserCity") %>'></asp:TextBox>

                   </EditItemTemplate>

                   <FooterTemplate>

                   <asp:TextBox ID="txtusercity" Width="100px" runat="server" ></asp:TextBox>

                   </FooterTemplate>

                   <ItemTemplate>

                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("UserCity") %>'></asp:Label>

                   </ItemTemplate>

               </asp:TemplateField>

              

                            

              <asp:TemplateField HeaderText="Addnew" ShowHeader="False">

               

               <FooterTemplate>

                 <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"CommandName="AddNew" Text="Add New"></asp:LinkButton>

               </FooterTemplate>         

               </asp:TemplateField>                        

           </Columns>

       </asp:GridView>

 

GridView looks like the below figure2.


 

tabl2.gif
 

 

Figure2

 

The following code snippet shows how to connect SQL database.

Dim conn As New SqlConnection("Data Source=.;uid=sa;pwd=Password$2;database=userinfo")

Dim ad As New SqlDataAdapter()

    Dim cmd As New SqlCommand()

    Dim dataTable As DataTable

 

This function is use to fetch data from the userinfo table, fills data in a DataTable object and find it to a GridView control using the DataSource property. In the end, the code calls the GridView. DataBind method to apply the binding.

 

Private Sub FillGrid()

        dataTable = New DataTable()

        cmd.Connection = conn

        cmd.CommandText = "SELECT * FROM userinfo"

        ad = New SqlDataAdapter(cmd)

        ad.Fill(dataTable)

        ResultGridView.DataSource = dataTable

        ResultGridView.DataBind()

    End Sub

 

RowCommand event

This RowCommand event is use to add a new row in database table.

Select GridView and press F4 to display property window Select event.

tabl3.gif
 

Figure3

Now double click on the RowCommand event and add the following code.

Protected Sub ResultGridView_RowCommand(ByVal sender As ObjectByVal e AsSystem.Web.UI.WebControls.GridViewCommandEventArgsHandles ResultGridView.RowCommand

        If e.CommandName.Equals("AddNew"Then

 

            Dim txtusername As TextBox =DirectCast(ResultGridView.FooterRow.FindControl("txtusername"), TextBox)

            Dim txtuserstate As TextBox =DirectCast(ResultGridView.FooterRow.FindControl("txtuserstate"), TextBox)

            Dim txtusercity As TextBox =DirectCast(ResultGridView.FooterRow.FindControl("txtusercity"), TextBox)

            cmd.Connection = conn

            cmd.CommandText = (("INSERT INTO userinfo(UserName,Userstate,UserCity) Values('" + txtusername.Text & "', '") + txtuserstate.Text & "', '") + txtusercity.Text & "')"

            conn.Open()

            cmd.ExecuteNonQuery()

            FillGrid()

            conn.Close()

        End If

    End Sub

Now run the application and test it.

tabl4.gif
 

Figure4

Now add the username, userstate and usercity in the corresponding TextBox.

tabl5.gif
 

Figure5

Now click on the Add New to add a new row in database table.

tabl6.gif
 

Figure6

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.