CODEWITHSUNDEEP
c#
BruceWayne61
BruceWayne61

Reputation: 13

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

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

Upvotes: 0

Views: 111

Answers (3)

Steve
Steve

Reputation: 11963

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

Upvotes: 1

DmitryK
DmitryK

Reputation: 5582

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

Upvotes: 1

user8958014
user8958014

Reputation:

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

Upvotes: 1

Related Questions