Click here to Skip to main content
15,921,371 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
For now, I have this:
//CHECK IF COMPLETE DUPLICATE
query = "SELECT * FROM TBL_FLAVORS WHERE flavor_name = @flavor_name AND flavor_supplierid = @supplier_id";
                    using (SqlConnection con = new SqlConnection(connstring))
                    {
                        con.Open();

                        using (SqlCommand cmd = new SqlCommand(query, con))
                        {

                            SqlDataReader read;
                            cmd.Parameters.AddWithValue("@flavor_name", txtFlavorName.Text.ToString());
                            cmd.Parameters.AddWithValue("@supplier_id", supplierid);
                            read = cmd.ExecuteReader();
                            if (read.Read())
                            {
                                MessageBox.Show("This flavor for this supplier already exists.");
                                return;
                            }
                            read.Close();
                        }
                    }


//CHECK IF ALMOST SIMILAR
//IF RECORD FOUND, RETURN
//OTHERWISE, PROCEED TO UPDATE/INSERT

                    query = "SELECT * FROM TBL_FLAVORS WHERE (flavor_name LIKE @flavor_name OR flavor_name LIKE @flavor_name2 OR FLAVOR_NAME LIKE @flavor_name3) AND flavor_supplierid = @supplier_id";
                    using (SqlConnection con = new SqlConnection(connstring))
                    {
                        con.Open();
                        
                            using (SqlCommand cmd = new SqlCommand(query, con))
                            {
                            string flavorname = "%" + txtFlavorName.Text.ToString()+ "%";
                            string flavorname2 = "" + txtFlavorName.Text.ToString() + "%";
                            string flavorname3 = "%" + txtFlavorName.Text.ToString() + "";
                            SqlDataReader read;
                                cmd.Parameters.AddWithValue("@flavor_name", flavorname);
                            cmd.Parameters.AddWithValue("@flavor_name2", flavorname2);
                            cmd.Parameters.AddWithValue("@flavor_name3", flavorname3);
                            cmd.Parameters.AddWithValue("@supplier_id", supplierid);
                                read = cmd.ExecuteReader();
                                if (read.Read())
                                {
                                DialogResult dialog = MessageBox.Show("It is possible that this flavor for this supplier already exists. Do you want to continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                                if (dialog == DialogResult.No)
                                {
                                    return;
                                }
                                }
                                read.Close();
                            }
                    }


This is for a final project. The panelist asked what if there was already an 'inch' and the user entered an 'innch.' It should be recognized as a duplicate. Or say there is a 'chocolate' in the database, it should ask for confirmation if the user enters 'chocolates'.

What I have tried:

SQL wildcard operators as stated above.
Posted
Updated 3-Oct-16 19:49pm
v2

Why don't you use or implement Autocomplete feature for textbox from where you take inputs from user?

Quote:
This is for a final project. The panelist asked what if there was already an 'inch' and the user entered an 'innch.' It should be recognized as a duplicate. Or say there is a 'chocolate' in the database, it should ask for confirmation if the user enters 'chocolates'.
And surely dear - No one is interested in what Panelist asked to you.
 
Share this answer
 
Comments
kmllev 4-Oct-16 1:40am    
I will consider implementing the Autocomplete feature, thanks!
And about the panelist's question... We had to defend the system as it is a requirement for our college thesis. They were pretty insistent on that. How can we possibly argue against that? The 'inch' and 'innch' should be considered as duplicate values?
MayurDighe 4-Oct-16 1:48am    
It's good you don't want to argue with panelist;)
But atleast you can find alternative solutions that panelist may think about your system.
It's hard to decide internally which is similar and which is not. If you want to automate this, you need to give some AI to your program.
Well, in SQL Server there are some options which can take you close to the what you wanted to achieve but accurate as it requires AI to decide, as I already said.

In application you definitely got much power to build your own logic and achieve more accurate result but let's see how we can quicky use some SQL Server functions to get something smilar.

SOUNDEX (Transact-SQL)[^]
DIFFERENCE (Transact-SQL)[^]
I would suggest to read these two documentation carefully.
Example (taken from above documenation link):
SQL
-- Using SOUNDEX  
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');  

Result:
----- -----   
S530  S530

We got same value as the result for both. Now let's use DIFFERENCE to compare these values.
SQL
-- Using DIFFERENCE  
SELECT DIFFERENCE ('Smith','Smythe');  

Result:
-----   
4


Ok, so what's "4" means here?
Quote:
The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.

SQL
-- Using DIFFERENCE  
SELECT DIFFERENCE ('Smith','Jones');  

Result:
-----   
2

Using your inputs-
SQL
SELECT DIFFERENCE ('chocolate','chocolates'),DIFFERENCE ('inch','innch');

Result:
----- -----  
4     4


Hope, you get an idea about what I was trying to explain.
Please let me know in case of any further queries.
 
Share this answer
 
Comments
kmllev 4-Oct-16 1:57am    
WOW.
I've never heard of this feature before, I'm extremely amazed, lol
But I wonder how I will be able to implement this in C#? How will I get the return value if there is no column name?
Suvendu Shekhar Giri 4-Oct-16 2:10am    
You can infact pass the column name as parameters to both SOUNDEX and DIFFERENCE functions.

Now let's say you want to check if user inputs a value similar to an existing value in the table then you can put a check before INSERT.
kmllev 4-Oct-16 11:07am    
I see. But how will I get the result or the return value of SOUNDEX and DIFFERENCE? I tried them in SQL Server and when they return the result there is no column name?
Hello

if you want to read from database and fetch data, you have to use:
SELECT DISTINCT (FieldName) FROM ... . if you want to get one "inch", your FieldName is related to "inch" Value.

or if you want that user Not to insert "chocolate" twice, you have to set the FielName as Primery Key.

hope ti works for you
best regards
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900