How do I check if row exists or not?

Question

This is my code to display/search a record in a database table. How do I put a validation whether the roecord exists or not? It is searching through the ID of a member. how should I show a message if the record does not exist?

 string connectionstring = "Server=Momal-PC\\MOMAL;Database=Project;Trusted_Connection=True;";
 SqlConnection conn = new SqlConnection();
 conn.ConnectionString = connectionstring;
 conn.Open();


  SqlDataAdapter sda = new SqlDataAdapter("Select * from Members where Number = '" + SearchID.Text + "'", conn);
  DataTable dtStock = new DataTable();

  sda.Fill(dtStock);
  dataGridView1.DataSource = dtStock;

  conn.Close();

score 7 · Accepted Answer · answered Apr 19 '13 at 05:18

7

if( 0 == dtStock.Rows.Count ) // does not exist

answered Apr 19 '13 at 05:18

Moho

15,457
1
30
31

RajeshKdev · Answer 2 · 2013-04-19T05:47:20.317

3

You can use like this:

If(dtStock.Rows.Count > 0) // If dtStock.Rows.Count == 0 then there is no rows exists.
{
    // Your Logic
}

See Here & Here. How to use Dataset and DataTables.

edited Apr 19 '13 at 05:47

answered Apr 19 '13 at 05:22

RajeshKdev

6,365
6
58
80

score 2 · Answer 3 · answered Apr 19 '13 at 05:24

2

You can use DataRowCollection.Count property.

Gets the total number of DataRow objects in this collection.

If(0 == dtStock.Rows.Count)
  Console.WriteLine("There are no rows in that datatable")

answered Apr 19 '13 at 05:24

Soner Gönül

97,193
102
206
364

score 2 · Answer 4 · answered Apr 19 '13 at 05:26

2

You can do something like this

    If(dtStock.Rows.Count > 0) 
    {
    //code goes here
    dataGridView1.DataSource = dtStock;
    }
    else
    {
    //Record not exists
    }

answered Apr 19 '13 at 05:26

Gajendra

1,291
1
19
32

score 2 · Answer 5 · answered Apr 19 '13 at 06:23

2

This SQL will be likely be much faster, as it will only return 0 or 1 rows to the client, rather than every matching row and every single column. Get out of the habit of using *

SELECT  1 As X WHERE EXISTS (
    Select 1 from Members where Number = '" + SearchID.Text + "')"

answered Apr 19 '13 at 06:23

Nick.Mc

18,304
6
61
91

Okay. I shall do this. Thank you. – momal Apr 21 '13 at 03:46

score 1 · Answer 6 · answered Aug 13 '15 at 16:15

 public static int RowCount()
        {

            string sqlConnectionString = @" Your connection string";
            SqlConnection con = new SqlConnection(sqlConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) AS Expr1 FROM Tablename", con);
            int result = ((int)cmd.ExecuteScalar());
            con.Close();
            return result;
    }

How do I check if row exists or not?

6 Answers6