0

I just want to insert some entries into a SQL Server database. But I need to check before inserting. The key point is that the table includes two columns id and name. The presented code should check both columns and prevent the entry to be inserted if the entry is exactly the same as in the SQL Server database.

Meaning that two columns of data table are same as two entries going to be inserted. I checked the forums but couldn't find any useful solution for my project. 

private void button4_Click(object sender, EventArgs e)
{
      try
      {
           objConnection.Open();
           string query = "INSERT INTO TutorTable(Tid, Tname) VALUES(N'" + tidTextBox.Text + "','" + tnameTextBox.Text + "')";

           SqlDataAdapter SDA = new SqlDataAdapter(query, objConnection);

           SDA.SelectCommand.ExecuteNonQuery();

           objConnection.Close();
           MessageBox.Show("ok!");
       }
       catch(Exception ex)
       {
           MessageBox.Show("error");
       }

       objConnection.Close();      
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Please read up on SqlCommandParameters. Your SQL statement is open for sql injection attacks – rene Jan 28 '18 at 17:28
  • 1
    Learn to use parameters and tag your question with the database you are using. – Gordon Linoff Jan 28 '18 at 17:29
  • 1
    @rene security issue is not in my consideration. I just want to check the duplicated data before inserting –  Jan 28 '18 at 17:30
  • And what ideas did you already try? Like doing a SELECT to see if the record already exists or apply constraints on the sql side. – Peter Bons Jan 28 '18 at 17:31

1 Answers1

1

This post explains a solution to the problem.

Essentially you can check the existence of a particular row first using:

IF NOT EXIST( SELECT 1 FROM TutorTable WHERE Tid = @tid)
BEGIN
    <your insert statement...>
END

(This is also using a parameter instead of directly values as suggested by rene)

You can then check the return value from ExecuteNonQuery to see how many rows were actually inserted.

Scott Perham
  • 2,410
  • 1
  • 10
  • 20
  • What does 1 mean before FROM TutorTable? –  Jan 28 '18 at 20:12
  • What does 1 mean before FROM TutorTable? –  Jan 28 '18 at 20:15
  • It returns the value "1" for each matched row rather than actually returning table data... https://stackoverflow.com/questions/7039938/what-does-select-1-from-do – Scott Perham Jan 28 '18 at 20:15
  • Thanks Scott. One more question, if i want to check two rows what should i do? Both Tid and Tname, i mean. –  Jan 28 '18 at 20:31
  • Just add `AND Tname = @Tname` to the where clause of the exist – Scott Perham Jan 28 '18 at 20:32
  • i have asked a question recently but no one answered it yet. Could you check my question and help me to find the correct answer? https://stackoverflow.com/q/48724301/9204293 –  Feb 11 '18 at 18:56