count records from sql table and display it on textbox

Question

I knew this is simple but I am not able to think. Displaying the count of a record from table and display it on textbox.

private void gMapControl1_Load_1(object sender, EventArgs e)
{
    SqlConnection conDatabase = new SqlConnection(constring);
    conDatabase.Open();
    DataTable db = new DataTable();
    SqlDataAdapter sda = new SqlDataAdapter(
        "select count(site) from [ICPS].[dbo].[excel GPS postcode]",
        conDatabase);

    sda.Fill(db);
    textBox29.Text = ;

    SqlDataAdapter sda = new SqlDataAdapter(
        "select count(site) from [ICPS].[dbo].[excel GPS postcode] "
            + "where Region = '1'",
        conDatabase);

    sda.Fill(db);
    textBox30.Text = ;
}

King King · Accepted Answer · 2013-10-16 13:53:40Z

5

You just need to use SqlCommand.ExecuteScalar instead of SqlDataAdapter like this:

SqlCommand com = new SqlCommand("select count(site) from [ICPS].[dbo].[excel GPS postcode]", conDatabase);
object count = com.ExecuteScalar();
if(count != null) textBox29.Text = count.ToString();
//The same for textBox30

More info on ExecuteScalar Note that the code I posted is just for the idea of using ExecuteScalar, it depends on your code style when working with ADO.NET, you may want to use some using statement, or reuse your command, ... in your own way you like.

answered Oct 16, 2013 at 13:53

King King

63.5k16 gold badges118 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Morten Anderson · Accepted Answer · 2013-10-16 13:56:48Z

3

using (SqlConnection conn = new SqlConnection(connString))
{
   conn.Open();
   cmd.CommandText = "select count(site) from [ICPS].[dbo].[excel GPS postcode]";
   Int32 count = (Int32) cmd.ExecuteScalar();
   textBox29.Text = count.ToString();
}

answered Oct 16, 2013 at 13:56

Morten Anderson

2,29115 silver badges20 bronze badges

Comments

Steve · Accepted Answer · 2013-10-16 14:26:53Z

When you expect just one value returned by your sql command, then use ExecuteScalar from the command object

string cmdText1 = "select count(site) from [ICPS].[dbo].[excel GPS postcode]";
using(SqlConnection conDatabase = new SqlConnection(constring))
using(SqlCommand cmd = new SqlCommand(cmdText, conDatabase))
{
      conDatabase.Open();
      int numRec = Convert.ToInt32(cmd.ExecuteScalar());
      textBox29.Text = numRec.ToString();
}

MSDN says

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

However I have noticed that you try to read the record count from two different queries.
So your code could also be written in this way to avoid a roundtrip to the database

string cmdText = "select count(site) from [ICPS].[dbo].[excel GPS postcode];" + 
                 "select count(site) from [ICPS].[dbo].[excel GPS postcode] " +
                 "where Region = '1'", ;
using(SqlConnection conDatabase = new SqlConnection(constring))
using(SqlCommand cmd = new SqlCommand(cmdText, conDatabase))
{
      conDatabase.Open();
      using(SqlDataReader reader = cmd.ExecuteReader())
      {
          reader.Read();
          int numRec1 = Convert.ToInt32(reader[0]);
          reader.NextResult();
          reader.Read();
          int numRec2 = Convert.ToInt32(reader[0]);
          textBox29.Text = numRec1.ToString();
          textBox30.Text = numRec2.ToString();
      }
}

In this way I have taken advantage of the ability of SQL Server to execute two or more commands separated by a semicolon.

Thanks everyone.. Sorry for the late response .. with your help issue has been resolved.

Suraj Singh · Accepted Answer · 2013-10-16 14:17:53Z

To fetch a value from datatable use row[columnName]

 SqlDataAdapter sda = new SqlDataAdapter(
    "select count(site) As siteCount  from [ICPS].[dbo].[excel GPS postcode]",
    conDatabase);


sda.Fill(db);
if(db !=null && db.Rows.Count > 0)
textBox29.Text =db["siteCount"].tostring();
else
textBox29.Text ="0";

However as King King suggested here why use datatable if you have to fetch just a single row and single column use 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. -MSDN

SqlCommand com = new SqlCommand("select count(site) from [ICPS].[dbo].[excel GPS postcode]", conDatabase);
object siteCount = com.ExecuteScalar();
if(siteCount != null) textBox29.Text = siteCount.ToString();

Collectives™ on Stack Overflow

count records from sql table and display it on textbox

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related