C# - SELECT Query number of rows affected is always -1

Question

I have this code and it always returns -1.I have three tables (a picture is more suggestive ): I want to see if the row is already in the ReservationDetails table, and if it's not to insert it. ``` try { SqlConnection conn = new SqlConnection... SqlCommand slct = new SqlCommand("SELECT * FROM ReservationDetails WHERE rID=@rID AND RNumber=@RNumber", conn); slct.Parameters.AddWithValue("@rID", (int)comboBox1.SelectedValue); slct.Parameters.AddWithValue("@RNumber", dataGridView1.SelectedRows[0].Cells[0].Value); int noRows;//counts if we already have the entry in the table conn.Open(); noRows = slct.ExecuteNonQuery(); conn.Close(); MessageBox.Show("The result of select="+noRows); if (noRows ==0) //we can insert the new row ```

Accepted Answer

You should

1) Change your TSQL to

```
 SELECT COUNT(*) FROM ReservationDetails WHERE ...
```

(better still, use `IF EXISTS` ...)

2) and use `ExecuteScalar()`:

```
noRows = (int) slct.ExecuteScalar();
```

Also: you will need to use a transaction (or some other atomic technique), or else someone could insert a row in-between you testing and trying to insert it...

All that said, it would be better to create a stored procedure that given your parameters, atomically tests and inserts into the table, returning `1` if successful, or `0` if the row already existed.

C# - SELECT Query number of rows affected is always -1

Solution

Problem