CODEWITHSUNDEEP
c#asp.netado.netoraclecommand
Ansul
Ansul

Reputation: 420

Get the value of Count from database rows in ASP.NET web app

The query is correct but I want to access the number of rows and show in the front-end page. This code is throwing an error.

   protected void Page_Load(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString_cw"].ConnectionString;
            OracleCommand cmd = new OracleCommand();
            OracleConnection con = new OracleConnection(constr);
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = @"SELECT COUNT (*) from dish";
            cmd.CommandType = CommandType.Text;

            DataTable dt = new DataTable("Dish");
            using (OracleDataReader sdr = cmd.ExecuteReader())
            {
                if (sdr.HasRows)
                {
                    dt.Load(sdr);
                    recordMsg.Text = sdr["count(*)"].ToString();
                }

            }
            con.Close();
        }

I am using Oracle as database and it is connected already.

Upvotes: 0

Views: 1277

Answers (2)

Usco
Usco

Reputation: 21

Use numeric index for the following line of code

recordMsg.Text = sdr["count(*)"].ToString();

Change it to...

recordMsg.Text = sdr[0].ToString();

Or:

Change the two line of code below:

cmd.CommandText = @"SELECT COUNT (*) from dish";
recordMsg.Text = sdr["count(*)"].ToString();

To read

cmd.CommandText = @"SELECT COUNT (*) as rCount from dish";
recordMsg.Text = sdr["rCount"].ToString();

Either option should work well for you. Note: The numeric index is zero because it's a zero-based index. I trust you understand this

Upvotes: 1

शेखर
शेखर

Reputation: 17604

As you need a single value there is no use of DataTable and DataReader you can simply use ExcuteScalar of command and get the values.

 command.ExecuteScalar();

More Reading https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=dotnet-plat-ext-5.0

or simply

recordMsg.Text=command.ExecuteScalar().ToString();

So the whole code can be written as

string constr = ConfigurationManager.ConnectionStrings["ConnectionString_cw"].ConnectionString;
OracleCommand cmd = new OracleCommand();
OracleConnection con = new OracleConnection(constr);
con.Open();
cmd.Connection = con;
cmd.CommandText = @"SELECT COUNT (*) from dish";
cmd.CommandType = CommandType.Text;
recordMsg.Text=command.ExecuteScalar().ToString();

It is also advisable to use using statement for better use of command and connection.

Upvotes: 1

Related Questions