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
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.
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.
Figure3
Now double click on the RowCommand event and add the following code.
Protected Sub ResultGridView_RowCommand(ByVal sender As Object, ByVal e AsSystem.Web.UI.WebControls.GridViewCommandEventArgs) Handles 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.
Figure4
Now add the username, userstate and usercity in the corresponding TextBox.
Figure5
Now click on the Add New to add a new row in database table.
Figure6