Repeated record...

Mfwamba Tshimanga

Hi!

I have a question. I am sure that is possible in Csharp.

How can I with my codes dont allow repeated record (for instance, I dont want see repeated "code" in my database). Need help on it please

I never do it. Want to learn how to do. Thanx!

private void btnSave_Click_1(object sender, EventArgs e)

{

conn = new SqlConnection(connstr);

comm = new SqlCommand();

conn.Open();

SqlParameter code = new SqlParameter("@code", SqlDbType.NChar);

comm.Parameters.Add(code);

code.Value = mycode.Text;

comm.Connection = conn;

comm.CommandText = "insert into classenumer values(@code)";

try

{

comm.ExecuteNonQuery();

MessageBox.Show("Good saving!");

mycode.Clear();

}

catch (Exception)

{

MessageBox.Show("Sorry, not saved");

}

finally

{

conn.Close();

}

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Munesh Sharma12y ago
private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                        String str = "server=MUNESH-PC;database=windowapp;UID=sa;password=123";
                        String query = "select * from data where username = '" + usertxt.Text + "'and password = '" +this.passtxt.Text + "'";
                        SqlConnection con = new SqlConnection(str);
                        SqlCommand cmd = new SqlCommand(query, con);
                        SqlDataReader dbr;
                        con.Open();
                        dbr = cmd.ExecuteReader();
                        int count = 0;
                        while (dbr.Read())
                        {
                            count = count + 1;
                        }
                        if (count == 1)
                        {
                            MessageBox.Show("username and password is correct");
                        }
                        else if (count > 1)
                        {
                            MessageBox.Show("Duplicate username and password", "login page");
                        }
                        else
                        {
                            MessageBox.Show(" username and password incorrect", "login page");
                        }
                    }


                // con.Close();

            catch (Exception es)
            {
                MessageBox.Show(es.Message);

            }
}
Vulpes12y ago
The code I posted above was only intended to reflect the position from:

if (count == 0)

onwards.

The earlier code should be as you already had it:

conn = new SqlConnection(connstr);
comm = new SqlCommand();
conn.Open();
SqlParameter code = new SqlParameter("@code", SqlDbType.NChar);
comm.Parameters.Add(code);
code.Value = mycode.Text;
comm.Connection = conn;
comm.CommandText = "select count(*) from classenumer where code = @code";
Israel12y ago
Now its running well. But when I click on my button's save its give this message: ExecuteScalar: CommandText property has not been initialized
Vulpes12y ago
I don't understand that because you'd declared the 'count' variable in the line before the 'if' statement:

// blah
int count = (int)comm.ExecuteScalar();
if (count == 0)
{
// blah
}
Israel12y ago
When I comment this line: with this line "if (count == 0)" in my code is saying "The name 'count' does not exist in the current context". Then without that line its saying only "not saved" even I put a different code.
Vulpes12y ago
It's not saving because you haven't changed the CommandText property to your Insert statement.

Try instead:

if (count == 0)
{
try
{
comm.CommandText = "insert into classenumer values(@code)";
comm.ExecuteNonQuery();
MessageBox.Show("Good saving!");
mycode.Clear();
}
catch (Exception)
{
MessageBox.Show("Sorry, not saved");
}
finally
{
conn.Close();
}
}
else
{
conn.Close(); // don't forget to close connection if no insertion was needed
}
Mfwamba Tshimanga12y ago

Vulves, I do try your code in mine as you can see. Its works. Means when the number for example is the same its doenst save. But when the code is different its says "good saving" as I do write in my code.
Then when I open my sql database to see if it's saved. Nothing have been saved. It's strange!
Please have a look:

private void btnSave_Click_1(object sender, EventArgs e)
{
{
conn = new SqlConnection(connstr);
comm = new SqlCommand();
conn.Open();
SqlParameter code = new SqlParameter("@code", SqlDbType.NChar);
comm.Parameters.Add(code);
code.Value = mycode.Text;
comm.Connection = conn;
//Here
comm.CommandText = "select count(*) from classenumer where code = @code";
int count = (int)comm.ExecuteScalar();
if (count == 0)
try
{
comm.ExecuteNonQuery();
MessageBox.Show("Good saving!");
mycode.Clear();
}
catch (Exception)
{
MessageBox.Show("Sorry, not saved");
}
finally
{
conn.Close();
}
}
}
Naveed Zaman12y ago
100% Agree with Vulpes perfect solution.
Vulpes12y ago
The easiest way is to make the code column a primary key (or one of the columns of the primary key) or to give it the unique constraint when you create your table in the database.

Failing that you'd have to run a Select query, prior to the Insert statement, to see whether a record whose code column with a value of mycode.Text already exists.

comm.CommandText = "select count(*) from classenumer where code = @code";
int count = (int)comm.ExecuteScalar();
if (count == 0)
{
// make your insertion
}