?
Solved

Check for duplicate records in a database before INSERT

Posted on 2005-03-02
9
Medium Priority
?
236 Views
Last Modified: 2010-04-16
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,"");
                  }
            }
0
Comment
Question by:HoomanJamshidiNHE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13439789
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
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13439818
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
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13439863
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 1000 total points
ID: 13439961
Is it working  Hooman JamshidiNHE?
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13440002
Oooops!  forgot to congert to string  


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

itsvtk
0
 

Author Comment

by:HoomanJamshidiNHE
ID: 13440572
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
 

Author Comment

by:HoomanJamshidiNHE
ID: 13441025
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
 

Author Comment

by:HoomanJamshidiNHE
ID: 13441048
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
 

Author Comment

by:HoomanJamshidiNHE
ID: 13441338
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question