Satnam Satnam - 22 days ago 6
ASP.NET (C#) Question

Making Sign up form using asp.net

I want that whenever the new user makes a sign up. C# code should check the value in database table whether the user with the same name exists or not . i am using sql-server for storing data and procedures to insert data. i am using a unique constraint in table and if the user with same name exists it should give a error message on the screen.

Procedure

CREATE PROCEDURE [dbo].[SigningIn]
@UserName nvarchar(90),
@Password nvarchar(90)
AS

insert into Signin(UserName,UserPassword) values
(@UserName,@Password)
RETURN


C# code

if(user name doesn't exists)
var command = new SqlCommand("SigningIn", connection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@UserName", UserNameBox.Text);
command.Parameters.AddWithValue("@Password", PasswordBox.Text);
command.ExecuteNonQuery();
connection.Close();
}
else
{
UserExists.Text = "User Already Exists";
}


Asp code

<asp:Label CssClass="col-sm-6 control-label " ID="UserName" runat="server" Text="User name"></asp:Label>
<div class="col-sm-6 ">
<asp:TextBox CssClass="form-control" placeholder="Pick a user name" ID="UserNameBox" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator CssClass="validators" ID="UserValidator" runat="server" ErrorMessage="Field is Required" ForeColor="#FF3300" ControlToValidate="UserNameBox" SetFocusOnError="True" ClientIDMode="Static" Display="Dynamic" ViewStateMode="Enabled"></asp:RequiredFieldValidator>
</div></div></div>
<div class="form-horizontal ">
<div class="form-group ">

<asp:Label CssClass="col-sm-6 control-label " ID="Password" runat="server" Text="Password"></asp:Label>
<div class="col-sm-6 ">
<asp:TextBox CssClass="form-control" placeholder="Enter password" ID="PasswordBox" runat="server" TextMode="Password" ></asp:TextBox>
<asp:RequiredFieldValidator CssClass="validators" ID="passwordValidator" runat="server" ErrorMessage="Field is Required" ForeColor="#FF3300" ControlToValidate="PasswordBox" SetFocusOnError="True" ClientIDMode="Static" Display="Dynamic" ViewStateMode="Enabled"> </asp:RequiredFieldValidator>

</div></div></div>
<div class="form-horizontal ">
<div class="form-group ">


<asp:Label CssClass="col-sm-6 control-label " ID="ConfirmLabel" runat="server" Text="Confirm Password"></asp:Label>
<div class="col-sm-6 ">
<asp:TextBox CssClass="form-control" placeholder="Confirm Password" TextMode="Password" ID="ConfirmBox" runat="server"></asp:TextBox> <asp:Label ID="UserExists" CssClass="validators" runat="server" Text="" ></asp:Label>
<asp:RequiredFieldValidator CssClass="validators" ID="confirmValidator" runat="server" ErrorMessage="Field is required" ControlToValidate="ConfirmBox" SetFocusOnError="True" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
<asp:CompareValidator ID="comparePassword" CssClass="validators" runat="server" ErrorMessage="Password doesn't match" SetFocusOnError="True" ControlToCompare="PasswordBox" ControlToValidate="ConfirmBox" ForeColor="#FF3300"></asp:CompareValidator>

Answer

You can actually set the condition directly from your procedure, which is to insert only when it doesn't exist, like so:

CREATE PROCEDURE [dbo].[SigningIn]
@UserName nvarchar(90),
@Password nvarchar(90)
 AS
BEGIN
IF NOT EXISTS(SELECT * FROM Signin WHERE UserName = @UserName)
BEGIN
INSERT INTO Signin(UserName,UserPassword) VALUES(@UserName,@Password);
END
END

Or to continue with C# code:

using(SqlConnection connection = new SqlConnection(connString)){
connection.Open();
bool userNameExists = false;

var command = 
new SqlCommand("SELECT COUNT(*) FROM Signin WHERE UserName = @UserName", connection);
command.Parameters.AddWithValue("@UserName",UserNameBox.Text.Trim();
userNameExists = (int)command.ExecuteScalar() > 0;

//if exists
if(userNameExists) {
UserExists.Text = "User Already Exists";
}else{
command = new SqlCommand("SigningIn", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UserName", UserNameBox.Text);
command.Parameters.AddWithValue("@Password", PasswordBox.Text);
command.ExecuteNonQuery();
connection.Close();
}
Comments