CODEWITHSUNDEEP
c#.net
Dhivya
Dhivya

Reputation: 21

How To get this Count Query in C#.net

the below coding getting execute but the value was not printing in screen

 protected void Button1_Click(object sender, EventArgs e)
    {
        if (CheckBox1.Checked)
        {

        string OIMSquery = "SELECT COUNT(name) AS PolicySold FROM TestDate WHERE name='divi'";
        SqlCommand OIMScmd = new SqlCommand(OIMSquery, OIMS_01);
        OIMS_01.Open();
        OIMScmd.ExecuteNonQuery();
        OIMS_01.Close();      

        }       
   }

Upvotes: 1

Views: 201

Answers (7)

Habib
Habib

Reputation: 223187

You should call ExectueScaler like

int count = (int) OIMScmd.ExecuteScalar();

ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Upvotes: 3

Jon Skeet
Jon Skeet

Reputation: 1499770

You've got a query. Therefore this call is inappropriate:

OIMScmd.ExecuteNonQuery();

Instead, you should be using ExecuteScalar():

int count = (int) OIMScmd.ExecuteScalar();

(It's possible that it'll return a long rather than an int - I'm not sure offhand.)

Additionally, you should use a using statement for the SqlCommand and create a new connection for each operation:

using (var connection = new SqlConnection(...))
{
    connection.Open();
    using (var command = new SqlCommand(query, connection))
    {
        int count = (int) command.ExecuteScalar();
        // Now use the count
    }
}

It's also not clear what kind of app this is - if it's in a local GUI (WinForms or WPF) you should not be performing database access on the UI thread. The UI will be frozen while the database access occurs. (If this is in a web application, it's even more important that you create a new database connection each time... you don't want two separate requests trying to use the same connection at the same time.)

Upvotes: 1

Vijaysing Gusinge
Vijaysing Gusinge

Reputation: 102

Try, OIMScmd.ExecuteReader(); method which returns the object of SqlDataReader class then read the value from it.

OR

use, Convert.ToInt32(OIMScmd.ExecuteScalar()) and then print the value.

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98740

You can use SqlCommand.ExecuteScalar method;

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

int i = (int)OIMScmd.ExecuteScalar();

Since this method returns object, you should implicity convert it to int.

 using (SqlConnection OIMS_01 = new SqlConnection(connString))
 {
      SqlCommand OIMScmd = new SqlCommand(OIMSquery, OIMS_01);
      try
      {
          OIMS_01.Open();
          int i = (int)OIMScmd.ExecuteScalar();
      }
      catch (Exception ex)
      {
          Console.WriteLine(ex.Message);
      }
 }

Upvotes: 0

Vishal Suthar
Vishal Suthar

Reputation: 17183

You need to use: ExecuteScalar()

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

int result = Convert.ToInt32(OIMScmd.ExecuteScalar());

Instead of 

OIMScmd.ExecuteNonQuery();

Upvotes: 0

Adrian Godong
Adrian Godong

Reputation: 8911

Use SqlCommand.ExecuteScalar() instead of ExecuteNonQuery() which returns nothing.

Upvotes: 0

John Woo
John Woo

Reputation: 263683

use ExecuteScalar() to fetch single value

string OIMSquery = "SELECT COUNT(name) AS PolicySold FROM TestDate WHERE name='divi'";
SqlCommand OIMScmd = new SqlCommand(OIMSquery, OIMS_01);
OIMS_01.Open();
int _result = Convert.ToInt32(OIMScmd.ExecuteScalar());
OIMS_01.Close();

Upvotes: 1

Related Questions