locked
Datatable RRS feed

  • Question

  • User-1499457942 posted

    Hi

     I have below code . There is no record in select statement but dt.rows.count still gives value 1

    SqlCommand sqlCommand = new SqlCommand("Select COUNT(*) from [Locations] where 
    =@Location", con);
                        //con.Open();
                        sqlCommand.Parameters.AddWithValue("@Location", Loc);
                        {
                           
                            SqlDataAdapter ad = new SqlDataAdapter(sqlCommand);
                            DataTable dt = new DataTable();
                            ad.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                            }
                            else
                            {
                                InsertInfo(Location, description);
                            }

    Thanks

    Thursday, August 2, 2018 6:10 PM

All replies

  • User-1034726716 posted

    Use the debugger and check the value returned from DataTable. You can also run your SELECT statement in SQL Management Studio to verify if your sql query really returns a value or not.

    Thursday, August 2, 2018 6:50 PM
  • User475983607 posted

    Perhaps "Loc" is declared as an int.  If "Loc" is not assigned a value it will default to 0.  Maybe there is a record in the Locations table with a 0 in the Code field. 

    Can you explain why, once again, you are not debugging your code?  If you set a breakpoint on...

    if (dt.Rows.Count > 0)

    you can see what is inside of dt.

    Thursday, August 2, 2018 7:10 PM
  • User-893317190 posted

    Hi JagjitSingh,

    Because count(*) still returns  data  0 , which will be added into the first row of your datatable , even though there is no data  in your database,so you couldn't use dt.Rows.Count>0 to check whether there is data in your datasource.

    I suggest you could write your code as follows.

    SqlCommand sqlCommand = new SqlCommand("Select COUNT(*) from [Locations] where 
    =@Location", con);          
                sqlCommand.Parameters.AddWithValue("@Location", Loc);
                {
    
                    SqlDataAdapter ad = new SqlDataAdapter(sqlCommand);
                    DataTable dt = new DataTable();
                    ad.Fill(dt);
                    if (Convert.ToInt32(dt.Rows[0][0]) > 0)
                    {
                    }
                    else
                    {
                     InsertInfo(Location, description); 
                    }
    
                }
    

    Besides ,If you  use count(*) which only returns single value  ,you could use sqlcommand object’s method ExecuteScalar().

    string sql = "Select COUNT(*) from [Locations] where 
    =@Location";
               using (SqlConnection sqlcon = new SqlConnection(con))
                {
                    using (SqlCommand com = new SqlCommand(sql, sqlcon))
                    {
                        com.Parameters.AddWithValue("@Location", Loc");
                        con.Open();
                    int count=  Convert.ToInt32( com.ExecuteScalar());
                        if (count > 0)
                        {
    
                        }
                        else
                        {
    
                        }
                    }
    
                }
    

    And the result.

    Best regards,

    Ackerly Xu

    Friday, August 3, 2018 9:48 AM
  • User-492460945 posted

    Hi,

    You are fetching count of records from select statement. even if there are no records in the table the count returns 0. So your datatable holds only one record always which displays the count of the records. So there is one record in the datatable which says 0 records in the database.

    Thanks,

    RajeshV.

    Monday, August 6, 2018 9:44 AM
  • User753101303 posted

    Hi,

    Seems you are confusing two ways to get a row count :

    • if data are not needed you can use a SELECT COUNT(*) FROM Table query that returns one row with the count result. With this you could use ExecuteScalar (whose purpose is to return the first column of the first row returned by a query)
    • if data are needed you can fetch all rows using a SELECT * FROM Table query and then get the client side row count from the client side structure to which data were loaded (here a DataTable)

    Here you are counting how many rows were returned by method #1 by using method #2 ie you should always get 1.

    Tuesday, August 7, 2018 8:27 PM