Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Check for duplicate records in a database before INSERT

Posted on 2005-03-02
9
Medium Priority
?
240 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

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!

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…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

577 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