Link to home
Start Free TrialLog in
Avatar of dodgerfan
dodgerfanFlag for United States of America

asked on

Check for duplicate values before letting user insert

I have an asp.net page that allows users to insert new records into a table. When they enter the data, I want to do a search of the empnum field to ensure that value does not exist already in the system. So before inserting, the page searches the users table for the empnum entered by the user. If it exits, show a message stating it already exists. If not, insert the record. What is the best way to do this? I'm using C# code behind.
Avatar of YZlat
YZlat
Flag of United States of America image

string strConn="your connection string here";
string sql="SELECT COUNT(*) FROM Table1 WHERE ID=1";


using (SqlConnection conn = new SqlConnection(strConn))
                {

                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        conn.Open();
                        int count = (int)cmd.ExecuteScalar();
                        if (count> 0)
                            //record exists
                        else
                           //insert here
                    }

                }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dodgerfan

ASKER

YZlat,
I'm grabbing the empum from a value entered into a textbox. The value can contain letters, so it's a string. Your code is erroring out on the line: int count = (int)cmd.ExecuteScalar(). It does not know what to do, I guess. Can I fix that?
can you post your sql statement?
your sql should be something like

sql="SELECT COUNT(*) FROM Table1 WHERE empnum='" + TextBox1.Text + "'";
if the field in your where clause is of type text, you will need single quotes around the value

WHERE empnum = 'Test'
Got it, thanks for the help. I just needed to get the syntax with those single and double quotes correct.