How to check if a value in a textbox already exists in a connected database

Question

So I am currently puzzled to the logic of my program, I have ran through it multiple times but I am not too sure. I basically want to be able to notify the user saying that the contact number they have entered already exists in the database. I have gotten close I am sure as I have done a lot of research but I do not think what I have done is actually checking the database but rather just telling it to match to anything that is in the textbox rather than checking the database.

Just to clarify I do not want to extract data from the database and put it into the textbox. Thanks.

Update: Two different programs for two different suggested solutions.

(Possible Solution 1) Updated contact number is unique method using COUNT:

private void CheckContactNumber()
{
    string checkContactNum = "SELECT COUNT(*) FROM Employee WHERE ContactNumber = " + addContactNum.Text + " "; //01234567890

    OleDbCommand cmd = new OleDbCommand(checkContactNum, conn);
    conn.Open();
    OleDbDataReader dr = cmd.ExecuteReader();

    if (dr.Read())
    {
        int countDup = (int)dr[0];

        if (countDup > 0 && addContactNum.Text != "")
        {
            err += "Contact number is already listed in the database\r\n";
            errorContactNum.Visible = true;
            uniqueContactNumber = false;
        }
        else if (countDup == 0 && addContactNum.Text != "")
        {
            errorContactNum.Visible = false;
            uniqueContactNumber = true;
        }
    }

    conn.Close();
}

(Possible Solution 2)Update to 2nd suggested solution:

if (err == "")
{
    // you miss s here
    string addEmployee = "if not exists(select LastName from Employee where LastName = @LastName)INSERT INTO Employee(FirstName, LastName, Role, DateOfHire, ContactNumber)" +"VALUES(@FirstName, @LastName, @Role, @DateOfHire, @ContactNumber)";

    OleDbCommand cmd = new OleDbCommand(addEmployee, conn);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    cmd.Parameters.Add("@FirstName", OleDbType.VarChar).Value = addFirstName.Text;
    cmd.Parameters.Add("@LastName", OleDbType.VarChar).Value = addLastName.Text;
    cmd.Parameters.Add("@Role", OleDbType.VarChar).Value = addRole.Text;
    cmd.Parameters.Add("@DateOfHire", OleDbType.VarChar).Value = addDateOfHire.Text;
    cmd.Parameters.Add("@ContactNumber", OleDbType.VarChar).Value = addContactNum.Text;

    conn.Open();

    // i removed the duplicated code
    if (cmd.ExecuteNonQuery() != 1)
    {
        err += "Contact number is already listed in the database\r\n";
                errorContactNum.Visible = true;
    }

    conn.Close();

    addFirstName.Text = String.Empty;
    addLastName.Text = String.Empty;
    addRole.Text = String.Empty;
    addContactNum.Text = String.Empty;
    addRole.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
    }
    //Save It
    else
    {
        MessageBox.Show(err);
    }
}

So this would be acceptable for adding to my database as all criterion is met but it doesnt accept it, I'm assuming it runs the else code right at the bottom of AddEmployee method

Here is an Image of my database which is linked to my program.

Solution 1

update your function from your end

private void CheckContactNumber()
    {

        string checkContactNum = "SELECT COUNT(*) FROM Employee WHERE ContactNumber = " + addContactNum.Text + " "; //01234567890

        OleDbCommand cmd = new OleDbCommand(checkContactNum, conn);
        conn.Open();
        OleDbDataReader dr = cmd.ExecuteReader();

        //if (dr.Read() && addContactNum.Text != "")
        if (dr.Read())
        {
            int count = (int)dr[0];
            if(count>0)
            {
                err += "Contact number is already listed in the database\r\n";
                errorContactNum.Visible = true;
                uniqueContactNumber = false;
            }

        }

        conn.Close();

    }

Updated Answer

    private void CheckContactNumber()
    {

        DataSet myDataSet = new DataSet();


        try
        {
            string strAccessSelect = "select count(*) from Employee where ContactNumber='" + addContactNum.Text + "'";
            OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, conn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

            conn.Open();
            myDataAdapter.Fill(myDataSet, "Employee");


        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
            return;
        }
        finally
        {
            conn.Close();
        }
        DataTable dt = myDataSet.Tables[0];
        if (dt != null)
        {
            if (int.Parse(dt.Rows[0][0].ToString()) > 0)
            {
                string err = "Contact Number Already exist..";
            }
        }
    }

Solution 2

You can merge two codes in one code , by using if not exist method in sql server like this

string addEmployee = "if not exists(select LastName from Employee where 
LastName=@LastName)INSERT INTO Employee (FirstName, LastName, Role, 
DateOfHire, ContactNumber)" +"VALUES (@FirstName, @LastName, @Role, @DateOfHire, @ContactNumber)";

if (cmd.ExecuteNonQuery()!=1){
// the employee exist in database
}

Comments

[Jordan]

Ive added your code into the method and stopped calling the other method, and removed the boolean variables linked with the other method but I get this error: Additional information: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

[Jordan]

I've update it but still has the same error How should I show you the code?

[Jordan]

I've update the code and it says this: Additional information: Syntax error (missing operator) in query expression 'ContactNumber ='. I then tried doing @addContactNum.Text

[Faraz Ahmed]

what DB you use for it?

[Faraz Ahmed]

see updated answer @JordanCoaten may it help you this is only check your condition, if you want to save data if COntactNumber not exist then you have to work on else statement

[Jordan]

Thank you for your answer but my validation isn't working correctly, is your code basically holding the first value that is entered into the textbox. I have ran through it and this seems to be the case, if so how do I edit this?

[Faraz Ahmed]

what's not working from your side? you wanted to edit your code?

[Jordan]

Sorry about the late reply I had to go out to see family, it identifies that a number is being used already but it also says that it isnt unique when it isnt being used.

[Jordan]

I think its basically only reading the first value been typed into the textbox.

[Jordan]

Ok so I used you code but tweaked it a bit from there, but before that the only problem with the code was to match my validation, all I had to add for it to work is an else statement after the nested IF. Thank you for your help!