Check for duplicate entries before insertion (C#.net)

Question

I have a textbox form that students fill out about their general information such as first and last name, city, state, etc. Sometimes a student can't remember if they filled out the form before and it will lead to duplicate entries in the ms-access database. Ideally I would like the code to first search the ms-access database for a matching first name AND last name on the same record before insertion. If there's a record that matches on both the entered first and last name fields then a script would run and say something like, "A matching record already exists, would you like to continue?" Clicking "Yes" would enter the record into a new row, clicking "Cancel" would not enter it into the database at all.

I started this code but I'm not sure if it's the right direction, any guidance would be appreciated, thanks.

using (OleDbConnection con = new OleDbConnection(constr))
    using (OleDbCommand com = new OleDbCommand("SELECT COUNT(*) FROM StudentList WHERE [FName] = @FName AND [LName] = @LName", con))
    {
        con.Open();
        using (OleDbDataReader myReader = com.ExecuteReader())
        {
            (This is where I am stuck)
        }
    }

Below is the current code for the submit button.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    {
        //Preforms insert statement on click to allow additions to the database
        DateTime CurrentDate;
        CurrentDate = DateTime.Now;

        string constr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\sites\schoolinfo\students_dev\App_Data\Studentdb.mdb";
        string cmdstr = "INSERT into StudentList(FName, LName, BDay, Gender, School, Grade, Address, APT, City, State, Zip, Email, Phone, CellPhone, ParentFName, ParentLName, ParentEmail) values(@FName, @LName, @BDay, @Gender, @School, @Grade, @Address, @APT, @City, @State, @Zip, @Email, @Phone, @CellPhone, @ParentFName, @ParentLName, @ParentEmail)";



        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand com = new OleDbCommand(cmdstr, con);
        {
            con.Open();
        }
        //The following fields are added from the student information to the corresponding database fields
        com.Parameters.AddWithValue("@FName", txtFirstName.Text);
        com.Parameters.AddWithValue("@LName", txtLastName.Text);
        com.Parameters.AddWithValue("@BDay", txtBirthDate.Text);
        com.Parameters.AddWithValue("@Gender", ddlGender.Text);
        com.Parameters.AddWithValue("@School", txtSchool.Text);
        com.Parameters.AddWithValue("@Grade", txtGrade.Text);

        //The following fields are added from the contact information to the corresponding database fields
        com.Parameters.AddWithValue("@Address", txtAddress.Text);
        com.Parameters.AddWithValue("@APT", txtApt.Text);
        com.Parameters.AddWithValue("@City", txtCity.Text);
        com.Parameters.AddWithValue("@State", ddlState.Text);
        com.Parameters.AddWithValue("@Zip", txtZip.Text);
        com.Parameters.AddWithValue("@Email", txtEmail.Text);
        com.Parameters.AddWithValue("@Phone", txtPhone.Text);
        com.Parameters.AddWithValue("@CellPhone", txtCellPhone.Text);
        com.Parameters.AddWithValue("@ParentFName", txtParentFName.Text);
        com.Parameters.AddWithValue("@ParentLName", txtParentLName.Text);
        com.Parameters.AddWithValue("@ParentEmail", txtParentEmail.Text);
        com.ExecuteNonQuery();
        con.Close();

        //End database connection
        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Student has been successfully added!')", true);
    }
}

Answer 1

if (txtYear.Text != "")
            {
                cmd = new SqlCommand("Select YearName from Year where YearName='" + txtYear.Text + "'", ConnOpen());
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                int i = ds.Tables[0].Rows.Count;
                if (i > 0)
                {
                    MessageBox.Show("Duplicate Values are not valid!!!");

                }

                else
                {
                    if (Classes.ClassDatabaseConnection.UserMessage("Are you srue you want to Add this Year!!!", "Confirm Updation") == true)
                    {
                        string insert = "insert into Year(YearName) values('" + txtYear.Text + "')";
                        int result = sqlrep.ExecuteNonQuery(insert);
                        if (result > 0)
                        {

                            System.Windows.Forms.MessageBox.Show("Year Added Successfully.", "Information", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);

                        }
                    }
                    dataLoad();
                }

            }

Answer 2

You should use ExecuteScalar when the return value of your query is only a single row with a single column. Of course the OleDbCommand that has parameters placeholders in its command text needs to have also a corresponding Parameters collection

using (OleDbConnection con = new OleDbConnection(constr))
using (OleDbCommand com = new OleDbCommand("SELECT COUNT(*) FROM StudentList WHERE [FName] = @FName AND [LName] = @LName", con))
{
    con.Open();
    com.Parameters.AddWithValue("@FName", txtFirstName.Text);
    com.Parameters.AddWithValue("@LName", txtLastName.Text);
    int count = Convert.ToInt32(com.ExecuteScalar());
    if(count == 0)
    {
        ... record doesn't exist
    }
    else
    {
        ... you have got count records 
    }

}

However let me say that this logic is rather weak. What happen if two students have the same First and Last name? What happen if someone mistype the name?. I think that you should require something more unique. Like a SSN or another ID provided by your school. (A Student Number or something alike)

Answer 3

couple of things:

1. you can set in your table the first name and last name as 1 primary key (yes it possible in ms-access). this way you will NEVER get any duplicate records

2. count(*) is not the best practice with databases.. but since you are dealing with ms-access

   using (OleDbDataReader myReader = com.ExecuteReader())
   {
       // reads the first and only column count(*) and convert it to a number
       if (Convert.ToInt16(myReader[0]) > 0)
       {
           // an entry already exists
       }
   }
   

Answer 4

using (OleDbConnection con = new OleDbConnection(constr))
    using (OleDbCommand com = new OleDbCommand("SELECT COUNT(*) FROM StudentList WHERE [FName] = @FName AND [LName] = @LName", con))
    {
        // Add your @Fname and @LName parameters here
        com.Parameters.AddWithValue("@FName", firstName);
        com.Parameters.AddWithValue("@LName", lastName);

        con.Open();
        using (OleDbDataReader myReader = com.ExecuteReader())
        {
            myReader.Read();
            int count = myReader.GetInt32(0);
            // return count > 0 or whatever to indicate that it exists
        }
    }