Getting data from database and display on the other web page

Database

For this article I have created a new database named LoginDB which contains the following table named Users in it.

Simple User Registration Form Example in ASP.Net

In the above table column UserId is set as primary key and it Identity property is set to true.

Note: The SQL for creating the database is provided in the attached sample code.

HTML Markup

The HTML Markup consists of some TextBox, their corresponding Validators and a Button. Other than RequiredField Validators there’s a CompareValidator to compare passwords and a RegularExpressionValidator to validate email address.

<tr>

Registration

th>

tr>

<tr>

<td>

Username

td>

<td>

<asp:TextBox ID="txtUsername" runat="server" />

td>

<td>

<asp:RequiredFieldValidator ErrorMessage="Required" ForeColor="Red" ControlToValidate="txtUsername"

runat="server" />

td>

tr>

<tr>

<td>

Password

td>

<td>

<asp:TextBox ID="txtPassword" runat="server" TextMode="Password" />

td>

<td>

<asp:RequiredFieldValidator ErrorMessage="Required" ForeColor="Red" ControlToValidate="txtPassword"

runat="server" />

td>

tr>

<tr>

<td>

Confirm Password

td>

<td>

<asp:TextBox ID="txtConfirmPassword" runat="server" TextMode="Password" />

td>

<td>

<asp:CompareValidator ErrorMessage="Passwords do not match." ForeColor="Red" ControlToCompare="txtPassword"

ControlToValidate="txtConfirmPassword" runat="server" />

td>

tr>

<tr>

<td>

td>

<td>

<asp:TextBox ID="txtEmail" runat="server" />

td>

<td>

<asp:RequiredFieldValidator ErrorMessage="Required" Display="Dynamic" ForeColor="Red"

ControlToValidate="txtEmail" runat="server" />

<asp:RegularExpressionValidator runat="server" Display="Dynamic" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"

ControlToValidate="txtEmail" ForeColor="Red" ErrorMessage="Invalid email address." />

td>

tr>

<tr>

<td>

td>

<td>

<asp:Button Text="Submit" runat="server" OnClick="RegisterUser" />

td>

<td>

td>

tr>

table>

Stored Procedure to insert the User details

The following stored procedure is used to insert the user details such as username, password and email address.

The stored procedure first checks whether the username supplied already exists, if yes then it will return negative 1 value.

Then the stored procedure checks whether the email address supplied already exists, if yes then it will return negative 2 value.

If both username and email address are valid then the record will be inserted and the auto-generated UserId will be returned by the stored procedure.

CREATE PROCEDURE [dbo].[Insert_User]

@Username NVARCHAR(20),

@Password NVARCHAR(20),

@Email NVARCHAR(30)

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT UserId FROM Users WHERE Username = @Username)

BEGIN

SELECT -1 -- Username exists.

END

ELSE IF EXISTS(SELECT UserId FROM Users WHERE Email = @Email)

BEGIN

SELECT -2 -- Email exists.

END

ELSE

BEGIN

INSERT INTO [Users]

([Username]

,[Password]

,[Email]

,[CreatedDate])

VALUES

(@Username

,@Password

,@Email

,GETDATE())

SELECT SCOPE_IDENTITY() -- UserId

END

Namespaces

You will need to import the following namespaces.

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

VB.Net

Imports System.Data

Imports System.Configuration

Imports System.Data.SqlClient

Inserting the User Details

The following event handler is raised when the submit button is clicked, here the values from the Registration Form’s TextBoxes are passed to the stored procedure and the stored procedure is executed.

The return value from the stored procedure is captured in a variable and then based on its value appropriate message is displayed using JavaScript Alert message box.

protected void RegisterUser(object sender, EventArgs e)

{

int userId = 0;

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand("Insert_User"))

{

using (SqlDataAdapter sda = new SqlDataAdapter())

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim());

cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());

cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim());

cmd.Connection = con;

con.Open();

userId = Convert.ToInt32(cmd.ExecuteScalar());

con.Close();

}

string message = string.Empty;

switch (userId)

{

case -1:

message = "Username already exists.\\nPlease choose a different username.";

break;

case -2:

message = "Supplied email address has already been used.";

break;

default:

message = "Registration successful.\\nUser Id: " + userId.ToString();

break;

}

ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + message + "');", true);

}

VB.Net

Protected Sub RegisterUser(sender As Object, e As EventArgs)

Dim userId As Integer = 0

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Using cmd As New SqlCommand("Insert_User")

Using sda As New SqlDataAdapter()

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim())

cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim())

cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim())

cmd.Connection = con

con.Open()

userId = Convert.ToInt32(cmd.ExecuteScalar())

con.Close()

End Using

Dim message As String = String.Empty

Select Case userId

Case -1

message = "Username already exists.\nPlease choose a different username."

Exit Select

Case -2

message = "Supplied email address has already been used."

Exit Select

Case Else

message = "Registration successful.\nUser Id: " + userId.ToString()

Exit Select

End Select

ClientScript.RegisterStartupScript([GetType](), "alert", (Convert.ToString("alert('") & message) + "');", True)

End Using

End Sub

Message Box when registration is successful

Message Box when username already exists

Message Box when email address already exists

User record inserted in table

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ananth G10y ago
Database
For this article I have created a new database named LoginDB which contains the following table named Users in it.

In the above table column UserId is set as primary key and it Identity property is set to true.
Note: The SQL for creating the database is provided in the attached sample code.

HTML Markup
The HTML Markup consists of some TextBox, their corresponding Validators and a Button. Other than RequiredField Validators there’s a CompareValidator to compare passwords and a RegularExpressionValidator to validate email address.
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<th colspan="3">
Registration
th>
tr>
<tr>
<td>
Username
td>
<td>
<asp:TextBox ID="txtUsername" runat="server" />
td>
<td>
<asp:RequiredFieldValidator ErrorMessage="Required" ForeColor="Red" ControlToValidate="txtUsername"
runat="server" />
td>
tr>
<tr>
<td>
Password
td>
<td>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password" />
td>
<td>
<asp:RequiredFieldValidator ErrorMessage="Required" ForeColor="Red" ControlToValidate="txtPassword"
runat="server" />
td>
tr>
<tr>
<td>
Confirm Password
td>
<td>
<asp:TextBox ID="txtConfirmPassword" runat="server" TextMode="Password" />
td>
<td>
<asp:CompareValidator ErrorMessage="Passwords do not match." ForeColor="Red" ControlToCompare="txtPassword"
ControlToValidate="txtConfirmPassword" runat="server" />
td>
tr>
<tr>
<td>
Email
td>
<td>
<asp:TextBox ID="txtEmail" runat="server" />
td>
<td>
<asp:RequiredFieldValidator ErrorMessage="Required" Display="Dynamic" ForeColor="Red"
ControlToValidate="txtEmail" runat="server" />
<asp:RegularExpressionValidator runat="server" Display="Dynamic" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
ControlToValidate="txtEmail" ForeColor="Red" ErrorMessage="Invalid email address." />
td>
tr>
<tr>
<td>
td>
<td>
<asp:Button Text="Submit" runat="server" OnClick="RegisterUser" />
td>
<td>
td>
tr>
table>

Stored Procedure to insert the User details
The following stored procedure is used to insert the user details such as username, password and email address.
The stored procedure first checks whether the username supplied already exists, if yes then it will return negative 1 value.
Then the stored procedure checks whether the email address supplied already exists, if yes then it will return negative 2 value.
If both username and email address are valid then the record will be inserted and the auto-generated UserId will be returned by the stored procedure.
CREATE PROCEDURE [dbo].[Insert_User]
@Username NVARCHAR(20),
@Password NVARCHAR(20),
@Email NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT UserId FROM Users WHERE Username = @Username)
BEGIN
SELECT -1 -- Username exists.
END
ELSE IF EXISTS(SELECT UserId FROM Users WHERE Email = @Email)
BEGIN
SELECT -2 -- Email exists.
END
ELSE
BEGIN
INSERT INTO [Users]
([Username]
,[Password]
,[Email]
,[CreatedDate])
VALUES
(@Username
,@Password
,@Email
,GETDATE())

SELECT SCOPE_IDENTITY() -- UserId
END
END

Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Inserting the User Details
The following event handler is raised when the submit button is clicked, here the values from the Registration Form’s TextBoxes are passed to the stored procedure and the stored procedure is executed.
The return value from the stored procedure is captured in a variable and then based on its value appropriate message is displayed using JavaScript Alert message box.
C#
protected void RegisterUser(object sender, EventArgs e)
{
int userId = 0;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Insert_User"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim());
cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim());
cmd.Connection = con;
con.Open();
userId = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
string message = string.Empty;
switch (userId)
{
case -1:
message = "Username already exists.\\nPlease choose a different username.";
break;
case -2:
message = "Supplied email address has already been used.";
break;
default:
message = "Registration successful.\\nUser Id: " + userId.ToString();
break;
}
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + message + "');", true);
}
}

VB.Net
Protected Sub RegisterUser(sender As Object, e As EventArgs)
Dim userId As Integer = 0
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("Insert_User")
Using sda As New SqlDataAdapter()
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Username", txtUsername.Text.Trim())
cmd.Parameters.AddWithValue("@Password", txtPassword.Text.Trim())
cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim())
cmd.Connection = con
con.Open()
userId = Convert.ToInt32(cmd.ExecuteScalar())
con.Close()
End Using
End Using
Dim message As String = String.Empty
Select Case userId
Case -1
message = "Username already exists.\nPlease choose a different username."
Exit Select
Case -2
message = "Supplied email address has already been used."
Exit Select
Case Else
message = "Registration successful.\nUser Id: " + userId.ToString()
Exit Select
End Select
ClientScript.RegisterStartupScript([GetType](), "alert", (Convert.ToString("alert('") & message) + "');", True)
End Using
End Sub

Message Box when registration is successful

Message Box when username already exists

Message Box when email address already exists

User record inserted in table
0
Salman Beg10y ago
Hi.
When you get the data from registration page and if you want to display the same data on another page then you have to save the data Session or you need to pass the data in queryString and also in that other page you need to retrieve the value. Apart from that so many other techniques are also exist. Please follow this article which will help you a lot.
http://www.nullskull.com/a/10400710/eight-different-ways-to-transfer-data-from-one-page-to-another-page.aspx
thanks
0
Manas Mohapatra10y ago
To design the User Registration page:
http://www.aspsnippets.com/Articles/Simple-User-Registration-Form-Example-in-ASPNet.aspx
http://www.dotnetawesome.com/2014/03/how-to-create-user-registration-page-in-mvc4.html
http://www.aspdotnet-suresh.com/2010/10/how-to-get-output-parameters-return-by.html
+1