Hi indradeo,
I have created a sample please take its reference.
HTML
Total Users :
<asp:Label ID="lblTotalUser" Text="" runat="server" /><br />
<table>
<tr>
<td>User ID</td>
<td><asp:TextBox ID="txtUserID" runat="server" ReadOnly="true" /></td>
</tr>
<tr>
<td>Name</td>
<td><asp:TextBox ID="txtUserName" runat="server" /></td>
</tr>
<tr>
<td>Surname</td>
<td><asp:TextBox ID="txtLastName" runat="server" /></td>
</tr>
<tr>
<td>City</td>
<td><asp:TextBox ID="txtLocation" runat="server" /></td>
</tr>
<tr>
<td colspan="2" align="center"><asp:Button ID="btnSubmit" Text="Submit" runat="server" OnClick="btnSubmit_Click" /></td>
</tr>
</table><br />
<asp:GridView ID="gvUsers" runat="server">
</asp:GridView>
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)
{
this.CountUsers();
this.ShowCountUsers();
this.BindUsers();
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO tblUsers VALUES(@UserID,@UserName,@LastName,@Location)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@UserID", txtUserID.Text.Trim());
cmd.Parameters.AddWithValue("@UserName", txtUserName.Text.Trim());
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text.Trim());
cmd.Parameters.AddWithValue("@Location", txtLocation.Text.Trim());
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
this.BindUsers();
this.CountUsers();
this.ShowCountUsers();
txtUserName.Text = string.Empty;
txtLastName.Text = string.Empty;
txtLocation.Text = string.Empty;
}
}
private void CountUsers()
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(UserId) AS 'UserID' FROM tblUsers", con))
{
cmd.CommandType = CommandType.Text;
con.Open();
lblTotalUser.Text = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
}
private void ShowCountUsers()
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(UserId) + 1 AS 'UserID' FROM tblUsers", con))
{
cmd.CommandType = CommandType.Text;
con.Open();
txtUserID.Text = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
}
private void BindUsers()
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT UserId,UserName,LastName,Location FROM tblUsers", con))
{
cmd.CommandType = CommandType.Text;
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
this.gvUsers.DataSource = dt;
this.gvUsers.DataBind();
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.CountUsers()
Me.ShowCountUsers()
Me.BindUsers()
End If
End Sub
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Using cmd As SqlCommand = New SqlCommand("INSERT INTO tblUsers VALUES(@UserID,@UserName,@LastName,@Location)", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@UserID", txtUserID.Text.Trim())
cmd.Parameters.AddWithValue("@UserName", txtUserName.Text.Trim())
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text.Trim())
cmd.Parameters.AddWithValue("@Location", txtLocation.Text.Trim())
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
Me.BindUsers()
Me.CountUsers()
Me.ShowCountUsers()
txtUserName.Text = String.Empty
txtLastName.Text = String.Empty
txtLocation.Text = String.Empty
End Using
End Sub
Private Sub CountUsers()
Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Using cmd As SqlCommand = New SqlCommand("SELECT COUNT(UserId) AS 'UserID' FROM tblUsers", con)
cmd.CommandType = CommandType.Text
con.Open()
lblTotalUser.Text = Convert.ToString(cmd.ExecuteScalar())
con.Close()
End Using
End Using
End Sub
Private Sub ShowCountUsers()
Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Using cmd As SqlCommand = New SqlCommand("SELECT COUNT(UserId) + 1 AS 'UserID' FROM tblUsers", con)
cmd.CommandType = CommandType.Text
con.Open()
txtUserID.Text = Convert.ToString(cmd.ExecuteScalar())
con.Close()
End Using
End Using
End Sub
Private Sub BindUsers()
Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Using cmd As SqlCommand = New SqlCommand("SELECT UserId,UserName,LastName,Location FROM tblUsers", con)
cmd.CommandType = CommandType.Text
Using sda As SqlDataAdapter = New SqlDataAdapter()
sda.SelectCommand = cmd
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Me.gvUsers.DataSource = dt
Me.gvUsers.DataBind()
End Using
End Using
End Using
End Sub
Screenshot
