How can I avoid having duplicate data in my database?

Question

I have a databse where the user can track new models coming into shop but they only keep one of each model, I'm not sure how to stop the user from repeating the same model. I've seen some previous answers on this site but I'm getting errors when using the code in my own.

  private void check_Click(object sender, EventArgs e)
    {
               string query = "INSERT INTO tbl_phones(model) VALUES(@model)";

               using (SqlConnection sqlconn = new SqlConnection(@""))
               using (SqlCommand comm = new SqlCommand(query, sqlconn))
                {
                    sqlconn.Open();
                    comm.Parameters.Add("@model, SqlDbType.NVarChar).Value = phoneinput.Text;

                    comm.ExecuteNonQuery();

here is the [answer](https://stackoverflow.com/a/108420/7174852) — Maksym Labutin, Mar 07 '19 at 10:30
You can do numerous things. You can set constrains on your tables you can query database before inserting or you can you something such as insert\on duplicate key. You should post the error and code it was throwing rather than ask such broad question. — Adrian, Mar 07 '19 at 10:30
Make it a primary key so the database will only allow one value for each type. — jdweng, Mar 07 '19 at 10:30
Welcome! What you want to do in case of there is an duplicate model? Ignore or throw error? — Kaushik, Mar 07 '19 at 10:30

Oliver Dalton · Accepted Answer · 2019-03-07T10:51:27.703

1

This should work (get a count of phones where model is entered, if is less than one then insert).

private void check_Click(object sender, EventArgs e)
{
    string insertQuery = "INSERT INTO tbl_phones(model) VALUES(@model)";
    string checkQuery = "SELECT COUNT(*) FROM tbl_phones WHERE model = @model";

    using (SqlConnection sqlconn = new SqlConnection(@""))
    {
        sqlconn.Open();

        SqlCommand checkCommand = new SqlCommand(checkQuery, sqlconn);
        checkCommand.Parameters.AddWithValue("@model", phoneinput.Text);

        if((int)checkCommand.ExecuteScalar() < 1)
        {
            SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconn);
            insertCommand.Parameters.AddWithValue("@model", phoneinput.Text);
            insertCommand.ExecuteNonQuery();
        }
    }
}

Edit - if you decide to set model to be a primary key, you can catch the exception like so -

private void check_Click(object sender, EventArgs e)
{
    string insertQuery = "INSERT INTO tbl_phones(model) VALUES(@model)";

    using (SqlConnection sqlconn = new SqlConnection(connectionString))
    {
        sqlconn.Open();

        SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconn);
        insertCommand.Parameters.AddWithValue("@model", "test");

        try
        {
            insertCommand.ExecuteNonQuery();
        }

        catch(SqlException ex)
        {
            if (ex.Number == 2627)
            {
                // Phone already exists, do some stuff
            }

            else throw;
        }
    }
}

edited Mar 07 '19 at 10:51

answered Mar 07 '19 at 10:36

Oliver Dalton

379
2
16

Thanks for the help, code seems to be working fine apart from one error at ExecuteScalar- is a method which is not valid in the given context, Any idea what is meant by that? – ohhey Mar 07 '19 at 10:51
Rather than executing two queries you could run one called [Merge](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/bb522522(v=sql.100)). Merge will insert a new record or update (if needed) the existing one. – Adrian Mar 07 '19 at 10:51
Did you copy my initial answer? I accidentally included .ExecuteScalar() twice - I've now corrected that. Also I would recommend you use the other solution in my answer - a primary key is probably a better way of doing it. – Oliver Dalton Mar 07 '19 at 10:54
Thanks for your contribution,yeah that was the error. I've marked your solution as the answer. I already have my ID column as the primary key, so I'm assuming if i need to link tables it's best to keep it as that? What would be the main difference between the methods you have shown? Also if I want an error to pop up to notify the person I'm guessing I just and an else statment at the end? – ohhey Mar 07 '19 at 11:07
For the first answer, you would just add an else statement and do what you need to do there. The main difference between the two options is the latter only has to run one query where as the initial has to run two, so I would probably recommend the latter if you are able to use a primary key (which in this case you are). – Oliver Dalton Mar 07 '19 at 11:09
Thanks ill look into the second option then as that sounds more efficent, lastly would it be ok to throw an error message in the brackets where you leave a comment //phone already exists? – ohhey Mar 07 '19 at 11:22
Yup - you can do whatever you need to do there (MessageBox.Show() as an example). – Oliver Dalton Mar 07 '19 at 11:31

How can I avoid having duplicate data in my database?

1 Answers1