Prevent Duplicate name

Question

4.00/5 (3 votes)

See more:

Hello ,

I using this code to add name in this table

C#

private void button1_Click(object sender, EventArgs e)
{
    string Coonstring = "datasource=localhost;port=3306;username=root;password=****;Charset=utf8";
    string cmd = "Insert into project.name_registry (name ) values('" + this.txt.Text + "');";
    MySqlConnection connectionDatabase = new MySqlConnection(Coonstring);
    MySqlCommand cmddata = new MySqlCommand(cmd, connectionDatabase);
    MySqlDataReader myreader;

    try
    {
        connectionDatabase.Open();
        myreader = cmddata.ExecuteReader();
        MessageBox.Show("Done");
        while (myreader.Read())
        {
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

I need when I press on this button ( Add button) check if the insert name found messagebox appear tell my the name exists and prevent the add. If not tell me the insert Done. How i can do this.

Regards

Posted 28-Apr-14 6:34am

Member 10724020

Add a Solution

Comments

[no name] 28-Apr-14 12:44pm

Do a SELECT first and if anything comes back, it already exists.

Member 10724020 28-Apr-14 13:01pm

Mate i'm a new in this, So can you explain more to me :)

joshrduncan2012 28-Apr-14 13:09pm

If you want to reply to a message, hover over the message and click the "reply" button instead of creating a new post, otherwise the person you are replying to will never get notified of your response unless he/she happens to check this on their own.

I echo Wes' statement. Do a SELECT statement on the db first to see if any records exist before doing the INSERT statement.

5 solutions

Solution 2

SELECT is easy - you know how to do that already, or you should before you get to INSERT statements.

But first...don't do anything like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
It's also a good idea to Dispose of Connection and Command objects - they are a scarce resource.
The easiest way is to use a using block:

C#

string Coonstring = "datasource=localhost;port=3306;username=root;password=****;Charset=utf8";
using (MySqlConnection connectionDatabase = new MySqlConnection(Coonstring))
    {
    try
        {
        connectionDatabase.Open();
        using (MySqlCommand select = new MySqlCommand("SELECT COUNT(*) FROM project.name_registry WHERE Name=@NM", connectionDatabase))
            {
            select.Parameters.AddWithValue("@NM", txt.Text);
            if (select.ExecuteScalar() != 0)
                {
                MessageBox.Show("Name exists");
                return;
                }
            }
        using (MySqlCommand cmddata = new MySqlCommand("INSERT INTO project.name_registry (name) VALUES(@NM)", connectionDatabase))
            {
            cmddata.Parameters.AddWithValue("@NM", txt.Text);
            cmddata.ExecuteNonQuery();
            MessageBox.Show("Done");
            }
        }
    catch (Exception ex)
        {
        MessageBox.Show(ex.Message);
        }
    }

Posted 28-Apr-14 8:19am

OriginalGriff

Updated 28-Apr-14 8:20am

v2

Comments

Member 10724020 28-Apr-14 14:45pm

Thanks For you but i got error in ( if (select.ExecuteScalar() != 0)) the error is ( Operator '!=' cannot be applide to operands of type'object' and 'int' ) <br>
So what i can do ?
I'm grateful for ur help

OriginalGriff 28-Apr-14 14:56pm

Good grief! You really are a beginner - I'd go back and reread your early stuff before you get into anything more complicated :laugh:
Try casting it to an int:
if ((int) select.ExecuteScalar() != 0)
My fault - I forgot...

Member 10724020 28-Apr-14 15:57pm

Actually, I'm very Ashamed from you. Cuz i asked you a lot. your code is working fine without any error but when i run it and press the ADD button messagbox appear to me tell me ( specified cast not valid )and not add or check anything. So where is the wrong here

Many thanks again to you .

Member 10724020 29-Apr-14 3:24am

@OriginalGriff. I'm grateful for your assistance. But kindly i need to complete this code and as i told you the code working fine but when i run it i get this error messagbox ( specified cast not valid ). What you can advice me ?
Many thanks

OriginalGriff 29-Apr-14 8:48am

Exactly what code are you using at the moment?

Kornfeld Eliyahu Peter 28-Apr-14 16:17pm

I always wonder what the reason using SQL that way. If some was asking me to do such a thing I was declare an unique index on name, and display custom error on failed insert...

Member 10724020 28-Apr-14 16:29pm

ٍSo what i can do about this ?

Solution 4

SQL injection is good point. it is better to have a parameterized query as indicated in Solution 2. But I would check the row existence in SQL as the following:

[updated("compiled")]

void UpdateTable(string connStr, string nameVal)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                string cmdStr = @"insert into project.name_registry(name) 
    select * from (select @pname) as tmp
    where not exists (select * from project.name_registry where name=@pname)";
                using (MySqlCommand cmd = new MySqlCommand(cmdStr, conn))
                {
                    cmd.Parameters.AddWithValue("@pname", nameVal);
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        Console.WriteLine("added");
                    }
                    else
                    {
                        Console.WriteLine("exists");
                    }
                }
            }
        }

Posted 28-Apr-14 9:04am

Vedat Ozan Oner

Updated 28-Apr-14 23:05pm

v2

Comments

Member 10724020 29-Apr-14 3:15am

thanks, But when i using this code i got many errors. So are u sure from this code?
Please your advice.

Vedat Ozan Oner 29-Apr-14 3:52am

actually I didn't test c# code. there are some obvious blanks in it. But I've checked command string (insert statement) for mysql. it worked on my table (mysql 5.5). if you test this statement on your table, and then embed it in c# code, I think it will do what you want.

Member 10724020 29-Apr-14 4:31am

The error in parakeets missing semicolon and like this. So are you have one tested and sure it working in C#. Thanks for your assistance.

Vedat Ozan Oner 29-Apr-14 5:05am

ok. updated the solution

Member 10724020 29-Apr-14 5:26am

@Vedat Ozan Thanks,
it Solved. Many thanks for your assistance.

Vedat Ozan Oner 29-Apr-14 13:08pm

you are welcome

Solution 6

if you want registration code at time of registration if that username already exist then alert user to choose another username for this you can refer to this video.

https://www.youtube.com/watch?v=_Nz4ynsDq3s&list=PL6n9fhu94yhXQS_p1i-HLIftB9Y7Vnxlo&index=92[^]

Posted 28-Apr-14 10:03am

Tarun Jaiswal

Comments

Member 10724020 28-Apr-14 16:47pm

No mate, I want when i insert the name in the (name_registry) table it check if the name exists it ignore the insert if not exist it add the name

Tarun Jaiswal 29-Apr-14 6:34am

you can use this store procedure and .net code
CREATE PROC spUser
@Name NVARCHAR(100)

AS
BEGIN
DECLARE @Count INT
DECLARE @ReturnCode INT

SELECT @Count = COUNT(Name) FROM tblUsers WHERE Name = @Name

IF @Count > 0
BEGIN
SET @ReturnCode = - 1
END
ELSE
BEGIN
SET @ReturnCode = 1
INSERT INTO tblname_registry VALUES ( @Name )
END

SELECT @ReturnCode AS ReturnValue
END

// If the Page has no validation errors
if (Page.IsValid)
{
// Read the connection string from web.config.
// ConfigurationManager class is in System.Configuration namespace
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spUser", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter username = new SqlParameter("@Name", txtName.Text);
cmd.Parameters.Add(name);

con.Open();
int ReturnCode = (int)cmd.ExecuteScalar();
if (ReturnCode == -1)
{
lblMessage.Text = "Name already in use, please choose another user name";
}
else
{
// whatever code you want to do can do here after successfull returning value
}
}
}

Member 10724020 29-Apr-14 11:20am

Many thanks for you

Solution 7

i will add the name field in database as unique key ,from the catch section i will check the already exists exception and show user "name already exists".I don't think the select query before an insertion in this case is an optimized way. gud luck ;-)

Posted 28-Apr-14 18:45pm

george4986

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ZurdoDev · Accepted Answer · 2014-04-28T08:15:00

Solution 1

As Wes said, you'll need to do a SELECT statement first.

For example:

SQL

SELECT * FROM name_registry WHERE name = @name

Don't concatenate strings to do your SQL because you are open to SQL injections.

C#

cmd.Parameters.AddWithValue("@name", txt.Text);

Then check if your datareader has rows. If it does, that means it already exists and then you can do whatever you need to do at that point.

Posted 28-Apr-14 8:15am

ZurdoDev

Comments

OriginalGriff 28-Apr-14 14:21pm

Snap! Well, mostly. His reader is irrelevant: INSERT operations don't return any rows! :laugh:

ZurdoDev 28-Apr-14 14:23pm

He wants to check if a record exists first. Thus the Select statement followed by a
if (dr.HasRows) ...

But you're right, for the insert he should just use ExecuteNonQuery on the cmd.

OriginalGriff 28-Apr-14 14:27pm

I wouldn't - just return the count and you don't need the reader at all. Saves a little bandwidth too, if you aren't using the rest of the row info.

ZurdoDev 28-Apr-14 14:32pm

True. You can do ExecuteScalar as well. Many ways to do it. You could also use IF EXISTS, SELECT COUNT(*), etc, etc. :)