How to capture duplicate values and show message box while reading from database?

Question

I've a DataGrid which uses a list object. I would like to prevent users from entering duplicate values into database.

My stored procedure already prevents duplicate data from entering into database but I would like to show a more descriptive message to the users explaining they can't enter duplicate values.

Below is my code where I'm inserting values via Stored Procedure:

using (var cn = ConnectionManager<SqlConnection>.GetManager(Database))
            using (var cmd = new SqlCommand("Save", cn.Connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@TableId", ReadProperty(TableIdProperty));
                cmd.Parameters.AddWithValue("@Q1", ReadProperty(Q1Property).ToUpper());
                cmd.Parameters.AddWithValue("@Q2", ReadProperty(Q2Property));

                using (var dr = new SafeDataReader(cmd.ExecuteReader()))
                {
                    //Would like to capture duplicates here if a record already exists with same Q1 and Q2 values 
                    if (dr.Read())
                        LoadProperties(dr);
                    else
                        dr.NextResult(); // sproc error happens here
                }
            }

Can you share the code of stored procedure? How it is preventing duplicate values? Does it raise any error if there are duplicate values? — Chetan, Aug 24 '17 at 15:05
It does raise an Application Error and 'rolls back' the insert. — Mihika, Aug 24 '17 at 15:40

score 0 · Accepted Answer · answered Aug 24 '17 at 17:51

0

If I got it right you have an Application where users can Insert new rows and you want to prevent duplicated entries.

If this is right you could add a Database Function that returns an ErrorCode and you could call this function using SqlCommand from your code.

answered Aug 24 '17 at 17:51

Dimos K

152
8

Yeah you're right. When they enter new rows I want to prevent duplicate entries. Could you please assist me with SqlCommand? – Mihika Aug 24 '17 at 18:11
You mean how to execute? – Dimos K Aug 24 '17 at 18:13
Yeah I meant I didn't understand how I'd write a database function that you recommended. – Mihika Aug 24 '17 at 18:18
See at https://stackoverflow.com/questions/14550486/how-to-create-a-function-in-sql-server for how to create an `function` in the database. You can write the same code you have in your stored procedure to check for duplicate entries – Dimos K Aug 24 '17 at 18:20
Just an suggestion. A good approach for not allowing double entries in a table is to create an unique index that contains the specific column. – Dimos K Aug 24 '17 at 22:02

How to capture duplicate values and show message box while reading from database?

1 Answers1