How do I check if value already exists in the database when I insert values using C#

Question

bağlanti.Open();

        SqlCommand komut = new SqlCommand("insert into RezervasyonKayıt (Rezervasyon_İçerik,Yemek_Tercihi,Kişi_Sayısı,İçecek_Tercihi,Giris_Ekstraları,Yetkili_Kisi,Rezervasyon_Tarihi)values(@İçerik,@Yemek,@Kisi,@İcecek,@Giris,@Yetkili,@RezTarih)", bağlanti);
        komut.Parameters.AddWithValue("@İçerik", comboBox1.SelectedItem);
        komut.Parameters.AddWithValue("@Yemek", comboBox2.SelectedItem);
        komut.Parameters.AddWithValue("@Kisi", comboBox3.SelectedItem);
        komut.Parameters.AddWithValue("@İcecek", comboBox4.SelectedItem);
        komut.Parameters.AddWithValue("@Giris", comboBox5.SelectedItem);
        komut.Parameters.AddWithValue("@Yetkili", comboBox6.SelectedItem);
        komut.Parameters.AddWithValue("@RezTarih", dateTimePicker1.Value);
        komut.ExecuteNonQuery();
        comboBox1.Text = "";
        comboBox2.Text = "";
        comboBox3.Text = "";
        comboBox4.Text = "";
        comboBox5.Text = "";
        comboBox6.Text = "";
        bağlanti.Close();
        XtraMessageBox.Show("Randevu Başarıyla Kayıt Edilmiştir", "Bilgi Mesajı", MessageBoxButtons.OK, MessageBoxIcon.Information);

Im trying to check only Rezervasyon_Tarihi if is it exist in database I will display to MessageBox("There is already Exist Rezervasyon at this Date! ";

string cmd=@"SELECT COUNT(*) From RezervasyonKayıt WHERE Rezervasyon_Tarihi=@RezTarih))";

        komut=new SqlCommand(cmd,bağlanti);
        komut.Parameters.AddWithValue("@RezTarih",dateTimePicker1.Value);
        bağlanti.Open();
        int records=(int)komut.ExecuteScalar();

        if (records==0)
        {
            komut.Parameters.Clear();
            cmd=@"insert into RezervasyonKayıt (Rezervasyon_İçerik,Yemek_Tercihi,Kişi_Sayısı,İçecek_Tercihi,Giris_Ekstraları,Yetkili_Kisi,Rezervasyon_Tarihi)values(@İçerik,@Yemek,@Kisi,@İcecek,@Giris,@Yetkili,@RezTarih)";
            komut=new SqlCommand(cmd,bağlanti);
            komut.Parameters.AddWithValue("@İçerik", comboBox1.SelectedItem);
            komut.Parameters.AddWithValue("@Yemek", comboBox2.SelectedItem);
            komut.Parameters.AddWithValue("@Kisi", comboBox3.SelectedItem);
            komut.Parameters.AddWithValue("@İcecek", comboBox4.SelectedItem);
            komut.Parameters.AddWithValue("@Giris", comboBox5.SelectedItem);
            komut.Parameters.AddWithValue("@Yetkili", comboBox6.SelectedItem);
            komut.Parameters.AddWithValue("@RezTarih", dateTimePicker1.Value);
            komut.ExecuteNonQuery();
            comboBox1.Text = "";
            comboBox2.Text = "";
            comboBox3.Text = "";
            comboBox4.Text = "";
            comboBox5.Text = "";
            comboBox6.Text = "";

            XtraMessageBox.Show("Randevu Başarıyla Kayıt Edilmiştir", "Bilgi Mesajı", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }else
        {
            Response.Write("Records Exists");
        }

Then do a select first. – OldProgrammer Nov 21 '17 at 20:25 — OldProgrammer, Nov 21 '17 at 20:25

score 1 · Answer 1 · edited Nov 21 '17 at 20:26

1

You can precede your insertion by a select request, to see if the entry you want to insert already exists.

edited Nov 21 '17 at 20:26

OldProgrammer

12,050
4
24
45

answered Nov 21 '17 at 20:26

Steve · Answer 2 · 2017-11-21T20:57:32.990

1

Two ways you can do it

First is to do a select before you insert, something like

SELECT COUNT(*) FROM .... WHERE .... SOMEID = SOMETHING

Do note that race condition could happen for this

The recommended way is to define a unique key on the ID and the insert will fail due to duplicated unique key exception. Catch that exception and display a msgbox

edited Nov 21 '17 at 20:57

answered Nov 21 '17 at 20:26

Steve

11,696
7
43
81

I do not recommend this approach as it can cause race conditions - unless you use a transaction. – Guilherme Nov 21 '17 at 20:45
@Guilherme thx for pointing out. edited the answer to indicate the unique key is the recommended way – Steve Nov 21 '17 at 20:58

score 1 · Accepted Answer · answered Nov 21 '17 at 20:34

1

You can check the presence of a particular key/record before trying to INSERT a new row. E.g. you can execute a "SELECT count(*)" type of statement beforehand to see records with the same value already exist. If this count is greater than zero then you display your error message.

Keep in mind that in highly transactional systems there might be a new record inserted between the moment you run the check and the moment (a few milliseconds later) when you are trying to insert a new record. So you might want to either wrap 2 statements into 1 transaction or be able to handle this scenario in code.

You may also consider adding a UNIQUE constraint to guarantee that only 1 record of that kind can exist in the table. More info: Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005

answered Nov 21 '17 at 20:34

DmitryK

5,542
1
22
32

I've Edited the my question I have problem on int records=(int)komut.ExecuteScalar(); An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll – BruceWayne61 Nov 21 '17 at 20:42
This is the correct recommendation (wrap both statements in one transaction). – Guilherme Nov 21 '17 at 20:49
Check what the value of this variable looks like: @RezTarih - I suspect that the way how you plug it into the SELECT query is wrong. Make sure that the SELECT statement is valid (especially the WHERE clause). Also why do you need the last 2 closing brackets? – DmitryK Nov 22 '17 at 00:15

How do I check if value already exists in the database when I insert values using C#

3 Answers3