Just don't repeat the same record please!

Hi!

Please I am trying to don't allow repetition of records. But I cannot resolv it.
It says it's saved but he didnt (the same datas or anothers when I open my sql server table I didnt find the record that was saved as he said).

conn = new SqlConnection(connstr);

comm = new SqlCommand();

conn.Open();

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

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

comm.Parameters.Add(data);

comm.Parameters.Add(classe);

data.Value = DateTime.Today;

classe.Value = codigoBarraStocks.Text;

comm.Connection = conn;

comm.CommandText = "select count(*) from stocks where classe = @classe";

int count = (int)comm.ExecuteScalar();

if (count == 0)

try

{

comm.ExecuteNonQuery();

MessageBox.Show("Saved succefully");

}

catch (Exception)

{

MessageBox.Show("Not saved");

}

finally

{

conn.Close();

}

Well, to add a record to the database you do of course need to use an INSERT statement rather than SELECT:

comm.CommandText = "insert into stocks (data, classe) values (@data, @classe)";
comm.ExecuteNonQuery();

So I assume you've done that somewhere else in your code and are now checking whether it's saved or not.

To check whether it's saved you'll need:

// blah

comm.CommandText = "select count(*) from stocks where classe = @classe";

try
{
int count = (int)comm.ExecuteScalar();
if (count == 1)
{
MessageBox.Show("Saved successfully");
}
else if (count > 1)
{
MessageBox.Show("Saved but duplicated");
}
else
{
MessageBox.Show("Not saved");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}

Incidentally, if the data column is an NChar rather than a DateTime in the database, then depending on format you'll need something like:

data.Value = DateTime.Today.ToString("yyyy/mm/dd");

To ensure there's no duplication you'd be better making one of the columns into a primary key or giving it a unique constraint.

1 Reply

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

Vulpes12y ago
Well, to add a record to the database you do of course need to use an INSERT statement rather than SELECT:

comm.CommandText = "insert into stocks (data, classe) values (@data, @classe)";
comm.ExecuteNonQuery();

So I assume you've done that somewhere else in your code and are now checking whether it's saved or not.

To check whether it's saved you'll need:

// blah

comm.CommandText = "select count(*) from stocks where classe = @classe";

try
{
int count = (int)comm.ExecuteScalar();
if (count == 1)
{
MessageBox.Show("Saved successfully");
}
else if (count > 1)
{
MessageBox.Show("Saved but duplicated");
}
else
{
MessageBox.Show("Not saved");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}

Incidentally, if the data column is an NChar rather than a DateTime in the database, then depending on format you'll need something like:

data.Value = DateTime.Today.ToString("yyyy/mm/dd");

To ensure there's no duplication you'd be better making one of the columns into a primary key or giving it a unique constraint.