Repeated record...

Question

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();

}

Vulpes · Answer

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 }

Munesh Sharma · Answer

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); } }

Vulpes · Answer

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;

Israel · Answer

Now its running well. But when I click on my button's save its give this message: ExecuteScalar: CommandText property has not been initialized

Vulpes · Answer

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 }

Israel · Answer

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.

Vulpes · Answer

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 Tshimanga · Answer

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 Zaman · Answer

100% Agree with Vulpes perfect solution.