how to use executereader to read one value

Question

I want to read an int value from my table. but I faced with error this is my code. please help me to edit my code.

    sqlc = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.Text;
    string username = HttpContext.Current.User.Identity.Name.ToString();

    cmd.CommandText = @"SELECT RemainedCharge "
                              + " FROM aspnet_Users "
                              + " WHERE UserName = @UserName ";
    cmd.Parameters.Add(new SqlParameter("@UserName", username));
    //string RemainedCharge;
    sqlc.Open();

    SqlDataReader rdr = cmd.ExecuteReader();


       // loop over all rows returned by SqlDataReader
       while(rdr.Read())
       {
          RemainedChargeLbl.Text=rdr.GetString(0);
       }

Sergey Berezovskiy · Answer 1 · 2013-11-30T07:18:33.753

3

To read one value you don't need reader. Use SqlCommand.ExecuteScalar Method which executes query and returns first column of the first row in result set returned by query:

int value = (int)cmd.ExecuteScalcar();

BTW it's better to create command object with sqlc.CreateCommand() - it creates appropriate command and automatically assigns connection to it.

edited Nov 30 '13 at 07:18

answered Nov 30 '13 at 07:13

Sergey Berezovskiy

232,247
41
429
459

score 2 · Answer 2 · edited May 23 '17 at 12:03

Use this code:

  using (var conn = new SqlConnection(SomeConnectionString))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT RemainedCharge "
                                  + " FROM aspnet_Users "
                                  + " WHERE UserName = @UserName ";
        cmd.Parameters.Add(new SqlParameter("@UserName", username);
        cmd.Parameters.AddWithValue("@id", index);
        using (var reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                learerLabel.Text = reader.GetString(reader.GetOrdinal("somecolumn"))
            }
        }

}

Sudhakar Tillapudi · Answer 3 · 2013-11-30T07:30:11.093

You are not assigning connection object sqlc to the SqlCommand.

Add this:

 cmd.Connection=sqlc;

Complete Solution:

sqlc = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection=sqlc;
string username = HttpContext.Current.User.Identity.Name.ToString();

cmd.CommandText = @"SELECT RemainedCharge "
                          + " FROM aspnet_Users "
                          + " WHERE UserName = @UserName ";
cmd.Parameters.Add(new SqlParameter("@UserName", username));
//string RemainedCharge;
sqlc.Open();

 RemainedChargeLbl.Text =((int) cmd.ExecuteScalar()).ToString();

how to use executereader to read one value

3 Answers3