Check for duplicate records in a database before INSERT

I have a form with two controls. a textbox called txtName and a button called button1.
The user inputs a name and clicks the button...that is meant to insert that name into the venues database. However, before that I want to check if that name or a name similar exists, and in doing so prompt the user to whether or not there is a similar value or if there is not. If there is a similar value then the user gets the chioce of continuing to insert it or not, and if there is no similar value then it will insert it without any prompts to the user.

I have created a public string called 'duplicate' and then with the myReader this value is filled with nothing (null) if no records are LIKE that value in the Select statement or with the value/record that it is similar to. So I have created an IF statement like so: if(duplicate == null) etc   --- but its not working...what is the best solution.

This is the main code:

private void button1_Click(object sender, System.EventArgs e)
            {
                  try
                  {
                        string myConnectionString = "Data Source=roman2;Initial Catalog=LPApplication;User Id=sa;Password=test;";
                        SqlConnection myConnection = new SqlConnection(myConnectionString);
                        string MSSQL1 = "SELECT * FROM venues where venue_Name LIKE '%"+txtName.Text+"%'";
                        
                        SqlCommand myCommand = new SqlCommand(MSSQL1);
                        myCommand.Connection = myConnection;
                        myConnection.Open();

                        SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        while(myReader.Read())
                        {
                              duplicate = myReader.GetString(1).ToString();
                        }
                        myReader.Close();
                        myCommand.Connection.Close();
                        
                        MessageBox.Show(duplicate);
                        if(duplicate == null)
                        {
                              MessageBox.Show("duplicate value found, do you want to add it anyway?");
                        }
                        else
                        {
                              MessageBox.Show("no duplicate value found");
                              //on this assumption i would add insert statement here
                        }
                  }
                  catch(Exception ex)
                  {
                        MessageBox.Show(ex.Message,"");
                  }
            }
HoomanJamshidiNHEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thandava VallepalliCommented:
try this one now


private void button1_Click(object sender, System.EventArgs e)
          {
               try
               {
                    string myConnectionString = "Data Source=roman2;Initial Catalog=LPApplication;User Id=sa;Password=test;";
                    SqlConnection myConnection = new SqlConnection(myConnectionString);
                    string MSSQL1 = "SELECT venue_Name FROM venues where venue_Name LIKE '%"+txtName.Text+"%'";
                   
                    SqlCommand myCommand = new SqlCommand(MSSQL1);
                    myCommand.Connection = myConnection;
                    myConnection.Open();

                    SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    while(myReader.Read())
                    {
                         duplicate = myReader.GetString(1).ToString();
                    }
                    myReader.Close();
                    myCommand.Connection.Close();
                   
                    MessageBox.Show(duplicate);
                    if(duplicate == null)
                    {
                         MessageBox.Show("duplicate value found, do you want to add it anyway?");
                    }
                    else
                    {
                         MessageBox.Show("no duplicate value found");
                         //on this assumption i would add insert statement here
                    }
               }
               catch(Exception ex)
               {
                    MessageBox.Show(ex.Message,"");
               }
          }

0
Thandava VallepalliCommented:
u can also try this

private void button1_Click(object sender, System.EventArgs e)
          {
               try
               {
                    string myConnectionString = "Data Source=roman2;Initial Catalog=LPApplication;User Id=sa;Password=test;";
                    SqlConnection myConnection = new SqlConnection(myConnectionString);
                    string MSSQL1 = "SELECT venue_Name FROM venues where venue_Name LIKE '%"+txtName.Text+"%'";
                   
                    SqlCommand myCommand = new SqlCommand(MSSQL1);
                    myCommand.Connection = myConnection;
                    myConnection.Open();

                    duplicate = myCommand.ExecuteScalar(CommandBehavior.CloseConnection);
                    myReader.Close();
                    myCommand.Connection.Close();
                   
                    MessageBox.Show(duplicate);
                    if(duplicate == null)
                    {
                         MessageBox.Show("duplicate value found, do you want to add it anyway?");
                    }
                    else
                    {
                         MessageBox.Show("no duplicate value found");
                         //on this assumption i would add insert statement here
                    }
               }
               catch(Exception ex)
               {
                    MessageBox.Show(ex.Message,"");
               }
          }
0
Thandava VallepalliCommented:
check this also......... if above doesn't work


private void button1_Click(object sender, System.EventArgs e)
          {
               try
               {
                    string myConnectionString = "Data Source=roman2;Initial Catalog=LPApplication;User Id=sa;Password=test;";
                    SqlConnection myConnection = new SqlConnection(myConnectionString);
                              string uname = txtName.Text;
                              uname = uname.Trim();
                    string MSSQL1 = "SELECT venue_Name FROM venues where lcase(venue_Name) LIKE '%" + uname.ToLower() + "%'";
                   
                    SqlCommand myCommand = new SqlCommand(MSSQL1);
                    myCommand.Connection = myConnection;
                    myConnection.Open();

                    duplicate = myCommand.ExecuteScalar(CommandBehavior.CloseConnection);
                    myReader.Close();
                    myCommand.Connection.Close();
                   
                    MessageBox.Show(duplicate);
                    if(duplicate == null)
                    {
                         MessageBox.Show("duplicate value found, do you want to add it anyway?");
                    }
                    else
                    {
                         MessageBox.Show("no duplicate value found");
                         //on this assumption i would add insert statement here
                    }
               }
               catch(Exception ex)
               {
                    MessageBox.Show(ex.Message,"");
               }
          }
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Thandava VallepalliCommented:
Is it working  Hooman JamshidiNHE?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thandava VallepalliCommented:
Oooops!  forgot to congert to string  


============================
duplicate = (string) myCommand.ExecuteScalar(CommandBehavior.CloseConnection);
============================

itsvtk
0
HoomanJamshidiNHEAuthor Commented:
it doesn't seem to work.

the problem lies in the if statement. if there are no LIKE values in the venues table then am I right in thinking that duplicate would be assigned a null value. If so then the following should work, but at the moment whether there is a value in the table similar already or if there is not I always get the message 'no duplicate value found' i.e. its always jumping to my else section. all my sql stuff is fine i would assume.

      if(duplicate != null)
      {
            MessageBox.Show("duplicate value found, do you want to add it anyway?");
      }
      else
      {
            MessageBox.Show("no duplicate value found");
            //on this assumption i would add insert statement here
      }

So how can I fix this if statement?
0
HoomanJamshidiNHEAuthor Commented:
I have solved the problem by 99%...in the following way: My first bit of code at the start of this page is now:

            private void button2_Click(object sender, System.EventArgs e)
            {
                  //null = duplicate;

                  try
                  {
                        
                        int x = txtName.Text.Length;

                        if(x > 0)
                        {
                              string myConnectionString = "Data Source=roman2;Initial Catalog=LPApplication;User Id=sa;Password=test;";
                              SqlConnection myConnection = new SqlConnection(myConnectionString);
                        
                              string MSSQL1 = "SELECT * FROM venues where venue_Name LIKE '%"+txtName.Text+"%'";
                        
                              SqlCommand myCommand = new SqlCommand(MSSQL1);
                              myCommand.Connection = myConnection;
                              myConnection.Open();

                              SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                              while(myReader.Read())
                              {
                                    duplicate = myReader.GetString(1).Length;
                              }
                        
                              myReader.Close();
                              myCommand.Connection.Close();
                        
                              MessageBox.Show(duplicate.ToString());
                                                            
                              int y = duplicate;

                              if(y > 0)
                              {
                                    MessageBox.Show("duplicate value found, do you want add it anyway?");
                              }
                              else
                              {
                                    MessageBox.Show("no duplicate. thank you!");
                                    //on this assumption i would add insert statement here
                              }
                        }
                        else
                        {
                              MessageBox.Show("You must enter a value to check.","");
                        }
                        
                  }
                  catch(Exception ex)
                  {
                        MessageBox.Show(ex.Message,"");
                  }
            }
0
HoomanJamshidiNHEAuthor Commented:
this works perfectly the first time the code runs through. However, for some reason if the duplicate value takes on a value greater than 0 it holds it in memory.

How can I reset  the int value (duplicate) to a null/empty value?
0
HoomanJamshidiNHEAuthor Commented:
ok... I so I must be stupid!

i have fixed that problem to, by adding

finally
{
         duplicate = 0;
}

The whole method works fine for checking LIKE values in a database.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.