在我的注册页面上，我想检查数据库中是否有用户名和电子邮件 [英] On My Registration Page I Want To Check If A Username And Email Are Already Taken In The Database
本文介绍了在我的注册页面上，我想检查数据库中是否有用户名和电子邮件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Security.Cryptography;
using System.Text;
using System.Data;
namespace GigGuide
{
public partial class VenueRegistration : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillLocationList();
FillSecurityList();
}
}
private void FillLocationList()
{
TownCity.Items.Clear();
string locationName = "select LocationName, LocationID from Location";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProjectDatabaseConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand(locationName, conn);
SqlDataReader reader1;
try
{
conn.Open();
reader1 = command.ExecuteReader();
while (reader1.Read())
{
ListItem LocationItem = new ListItem();
LocationItem.Text = reader1["LocationName"].ToString();
LocationItem.Value = reader1["LocationID"].ToString();
TownCity.Items.Add(LocationItem);
}
reader1.Close();
}
catch (Exception ex)
{
Response.Write("error" + ex.ToString());
}
finally
{
conn.Close();
}
}
private void FillSecurityList()
{
SecurityQuestion.Items.Clear();
string securityQuestion = "select Question, SecurityQuestionID from SecurityQuestion";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProjectDatabaseConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand(securityQuestion, conn);
SqlDataReader reader1;
try
{
conn.Open();
reader1 = command.ExecuteReader();
while (reader1.Read())
{
ListItem SecurityList = new ListItem();
SecurityList.Text = reader1["Question"].ToString();
SecurityList.Value = reader1["SecurityQuestionID"].ToString();
SecurityQuestion.Items.Add(SecurityList);
}
reader1.Close();
}
catch (Exception ex)
{
Response.Write("error" + ex.ToString());
}
finally
{
conn.Close();
}
}
protected void Submit_Click(object sender, EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProjectDatabaseConnectionString"].ConnectionString);
if (!string.IsNullOrEmpty(UserName.Text))
{
conn.Open();
string checkUser = "select * from Venue where UserName=@Name";
SqlCommand command = new SqlCommand(checkUser, conn);
command.Parameters.AddWithValue("@Name", UserName.Text);
SqlDataReader dr = command.ExecuteReader();
if (dr.HasRows)
{
checkusername.Visible = true;
lblStatus.Text = "UserName Already Taken";
conn.Close();
}
else
{
conn.Open();
string getLocationQuery = "select LocationID from Location where LocationName = '" + TownCity.SelectedItem.ToString() + "'";
SqlCommand command1 = new SqlCommand(getLocationQuery, conn);
int locID = (int)command1.ExecuteScalar();
if (locID != null)
{
locID = Convert.ToInt32(locID);
string getSecurityQuestionQuery = "select SecurityQuestionID from SecurityQuestion where Question = '" + SecurityQuestion.SelectedItem.ToString() + "'";
command1 = new SqlCommand(getSecurityQuestionQuery, conn);
int questID = (int)command1.ExecuteScalar();
if (questID != null)
{
questID = Convert.ToInt32(questID);
string guidVenue = Guid.NewGuid().ToString();
guidVenue = guidVenue.Replace("-", string.Empty);
string uniqueValue = guidVenue.Substring(0, 6);
uniqueValue = "V" + uniqueValue;
if (uniqueValue != null)
{
string insertQuery = "insert into Venue (VenueID,VenueEmail,Password,UserName,VenueTelephoneNumber,LocationID,ContactPerson,SecurityQuestionID,SecurityAnswer) Values (@ID, @Email, @Password, @UName, @Telephone,@LocID, @ContactPerson, @SecurityquestID, @Securityanswer)";
command1 = new SqlCommand(insertQuery, conn);
command1.Parameters.AddWithValue("@ID", uniqueValue);
command1.Parameters.AddWithValue("@Email", Email.Text);
command1.Parameters.AddWithValue("@Password", Password.Text);
command1.Parameters.AddWithValue("@UName", UserName.Text);
command1.Parameters.AddWithValue("@Telephone", Telephone.Text);
command1.Parameters.AddWithValue("@LocId", locID);
command1.Parameters.AddWithValue("@ContactPerson", ContactPerson.Text);
command1.Parameters.AddWithValue("@SecurityQuestID", questID);
command1.Parameters.AddWithValue("@SecurityAnswer", SecurityAnswer.Text);
command1.ExecuteNonQuery();
Response.Write("Registration is successful");
conn.Close();
}
}
}
}
conn.Close();
if (!string.IsNullOrEmpty(Email.Text))
{
//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ProjectDatabaseConnectionString"].ConnectionString);
conn.Open();
string checkEmailAddress = "select * from Venue where VenueEmail=@email";
command = new SqlCommand(checkEmailAddress, conn);
command.Parameters.AddWithValue("@email", Email.Text);
SqlDataReader dr1 = command.ExecuteReader();
//conn.Close();
if (dr1.HasRows)
{
checkEmail.Visible = true;
lblStatus1.Text = "Email Already exists";
//conn.Close();
}
else
{
conn.Open();
string getLocationQuery = "select LocationID from Location where LocationName = '" + TownCity.SelectedItem.ToString() + "'";
SqlCommand command1 = new SqlCommand(getLocationQuery, conn);
int locID = (int)command1.ExecuteScalar();
if (locID != null)
{
locID = Convert.ToInt32(locID);
string getSecurityQuestionQuery = "select SecurityQuestionID from SecurityQuestion where Question = '" + SecurityQuestion.SelectedItem.ToString() + "'";
command1 = new SqlCommand(getSecurityQuestionQuery, conn);
int questID = (int)command1.ExecuteScalar();
if (questID != null)
{
questID = Convert.ToInt32(questID);
string guidVenue = Guid.NewGuid().ToString();
guidVenue = guidVenue.Replace("-", string.Empty);
string uniqueValue = guidVenue.Substring(0, 6);
uniqueValue = "V" + uniqueValue;
if (uniqueValue != null)
{
string insertQuery = "insert into Venue (VenueID,VenueEmail,Password,UserName,VenueTelephoneNumber,LocationID,ContactPerson,SecurityQuestionID,SecurityAnswer) Values (@ID, @Email, @Password, @UName, @Telephone,@LocID, @ContactPerson, @SecurityquestID, @Securityanswer)";
command1 = new SqlCommand(insertQuery, conn);
command1.Parameters.AddWithValue("@ID", uniqueValue);
command1.Parameters.AddWithValue("@Email", Email.Text);
command1.Parameters.AddWithValue("@Password", Password.Text);
command1.Parameters.AddWithValue("@UName", UserName.Text);
command1.Parameters.AddWithValue("@Telephone", Telephone.Text);
command1.Parameters.AddWithValue("@LocId", locID);
command1.Parameters.AddWithValue("@ContactPerson", ContactPerson.Text);
command1.Parameters.AddWithValue("@SecurityQuestID", questID);
command1.Parameters.AddWithValue("@SecurityAnswer", SecurityAnswer.Text);
command1.ExecuteNonQuery();
Response.Write("Registration is successful");
conn.Close();
}
}
}
}
conn.Close();
}
}
}
catch (Exception ex)
{
Response.Write("Error " + ex.ToString());
}
}
}
}
推荐答案
我正在直接回答这个问题从标题。你可以使用这样的存储过程：
I am answering this question directly from title. You can make use of stored procedure like this:
CREATE PROCEDURE insert_UserDetails
(
@email varchar(50),
@username varchar(20)
)
AS
BEGIN
if exists (your query to check the existence of specified value)
////
else
insert into tbl_ins(email, username) values(@email,@username)
END
你应该叫这个存储过程与电子邮件和用户名作为参数。这将检查具有指定值（电子邮件，用户名）的数据的存在。如果它不存在，那么它将被插入。
You are supposed to call this stored procedure with email and username as parameters.This will check the existance of data with specified values(email,username). If it doesn't exists,then it will get inserted.
你好，
在插入之前检查用户名是否已经是否接受。
Hi,
Before inserting check whether the username is already taken or not.
protected bool UserNameExists()
{
bool isExists = false;
try
{
myconnection.Open();
string cmd = "SELECT EmpUserName FROM EmployeesDetails where EmpUserName= '" + txtEmpUserName.Text + "'";
SqlCommand command = new SqlCommand(cmd, myconnection);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
isExists = true;
}
}
catch (Exception message)
{
lblCretaeException.Text = message.ToString();
}
finally
{
myconnection.Close();
}
return isExists;
}
如果用户名不在数据库中，请添加详细信息。
If username is not in the databse add that details.
protected void AddData()
{
if (!UserNameExists())
{ try
{
//Insertion//
}
catch (Exception message)
{
}
finally
{
myconnection.Close();
}
}
好运
Good Luck
您可以像这样计算正在插入的数据的值：
You can count the value of data that is being inserted like this:
string constr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
SqlConnection con = new SqlConnection(constr);
string sql1 = "SELECT COUNT (email) FROM client WHERE email = '" + txtid.Text + "' ";
SqlCommand cmd = new SqlCommand(sql1, con);
con.Open();
int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());
if (temp >0)
{
//show error message
}
这篇关于在我的注册页面上，我想检查数据库中是否有用户名和电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
