How to check if a value already exists in my database and show a validation message

Question

I have the below code, that connects to a Sql database and insert's data into a table :

string firstNameV = txtFname.Text;
string surnameV = txtSname.Text;
string emailV = txtEmail.Text;

SqlConnection conn = new   SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ToString());

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "INSERT INTO EmailSignUp (Title,FirstName,Surname,Email,EstablishmentType,Interests) VALUES (@Title,@FirstName,@Surname,@Email,@EstablishmentType,@Interests)";

cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = title;
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = firstNameV;
cmd.Parameters.Add("@Surname", SqlDbType.NVarChar).Value = surnameV;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = emailV;
cmd.Parameters.Add("@EstablishmentType", SqlDbType.NVarChar).Value = eType;
cmd.Parameters.Add("@Interests", SqlDbType.NVarChar).Value = ins;

cmd.Connection = conn;

conn.Open();

cmd.ExecuteNonQuery();
conn.Close();

How do I check if an email being entered in the "txtEmail" text box already exists in my database, in the email column and then alert message saying email already exists so it doesn't get inserted into my database?

Call a stored procedure instead of inserting directly into the database. Test within the procedure to see if the email address already exists and return a value if it does/doesn't and whether the insert went okay. — Martin Smellworse
– Martin Smellworse, Commented Mar 16, 2014 at 22:05
And, put the opening of the connection into a try statement with the closing of the connection in finally. — Martin Smellworse
– Martin Smellworse, Commented Mar 16, 2014 at 22:07

Rob Watts · Accepted Answer · 2014-05-06 10:30:12Z

6

Call this method in required textbox or area

public void EmailCheck()
    {
        string constring = ConfigurationManager.ConnectionStrings["ConnData"].ConnectionString;
        SqlConnection con = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("Select * from EmailSignUp where EmailId= @EmailId", con);
        cmd.Parameters.AddWithValue("@EmailId", this.txtEmail.Text);
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            if (dr.HasRows == true)
            {
                MessageBox.Show("EmailId = " + dr[5].ToString() + " Already exist");
                txtEmail.Clear();
                break;
            }
        }

    }

edited May 6, 2014 at 10:30

Rob Watts

7,1563 gold badges42 silver badges60 bronze badges

answered May 6, 2014 at 10:03

Varun Kurumella

713 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

voddy · Accepted Answer · 2014-03-16 22:49:40Z

0

Try this

cmd.CommandText = "IF NOT EXISTS(SELECT * FROM EmailSignUp WHERE Email = '" 
+ txtEmail.Text + "') 
BEGIN
INSERT INTO EmailSignUp (Title,FirstName,Surname,Email,EstablishmentType,Interests) VALUES (@Title,@FirstName,@Surname,@Email,@EstablishmentType,@Interests) 
END";

edited Mar 16, 2014 at 22:49

answered Mar 16, 2014 at 22:40

voddy

1,0101 gold badge12 silver badges22 bronze badges

Comments

SRJ · Accepted Answer · 2014-03-17 07:21:32Z

0

Call a stored Procedure and inside the stored procedure you can check before insert

 IF NOT EXISTS(SELECT * FROM EmailSignUp WHERE Email =@email)   
 Begin
insert query here
 end

In another way you can check it in text changed event also

answered Mar 17, 2014 at 7:21

SRJ

1981 gold badge4 silver badges23 bronze badges

Comments

Sinoy Siby · Accepted Answer · 2015-12-08 05:33:40Z

0

Create a procedure on SQL server and check whether the name exists or not

    CREATE PROCEDURE Procedure_Name 
@mystring varchar(100),
@isExist bit out
AS
BEGIN
    if exists(select column1 from tblTable1 where column1=@mystring)
    begin
    select @isExist=1
    end
    else
    begin
    select @isExist=0
    end


END
GO

This is a sample procedure. If @isExist=1 that means the value exist.otherwise not. create a method to call this procedure and go on... Happy Coding

answered Dec 8, 2015 at 5:33

Sinoy Siby

394 bronze badges

Comments

ZygD · Accepted Answer · 2021-10-01 07:06:30Z

This works for me:

Create a function Called CheckMail(string email)

public bool CheckMail(string email)

        {
            SqlConnection con = new SqlConnection("Data Source=*******; Initial Catalog=Your Database Name; Persist Security Info=True;User ID=****; Password=******");

                SqlCommand cmd = new SqlCommand("select email from  Table Name where email='"+email+ "'",con);

                con.Open();

            SqlDataReader sdr = cmd.ExecuteReader();

            if (sdr.Read())
            {
                return false;
            }
            else
            {
                return true;
            }            
        }

 

Then Implement in Button Click as 

Pass Textbox value in function that were created..

 

if (CheckMail(EmailTxt.Text))  
                {

Write Your insert code to database 

}

else

{

Error Message or Alert to Show Already Exists in database

}

Collectives™ on Stack Overflow

How to check if a value already exists in my database and show a validation message

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related