How to count each record from database and display count value with each record in gridview

Question

1.00/5 (1 vote)

See more:

I am trying to count no of record from database and display that count value with each record of gridview.

For Ex:-
database record
1 CLASS 8 A01 Adi 2017 03/15/2017 11:30:46 AM P
2 CLASS 8 A02 Adi 2017 04/15/2017 11:30:46 AM P
3 CLASS 8 A03 Ram 2017 03/15/2017 11:30:46 AM A

ANd Display in gridview:-

CLASS ROLL NAME BATCH DATE PRESENT/ABSENT ATTENDED LECTURES
CLASS8 A01 Adi 2017 03/15/2017 Checkbox 2
CLASS8 A02 Ram 2017 03/15/2017 Checkbox 0

Please Reply me soon..
I have unable to display attended lecture value..

What I have tried:

<pre>
    void gvattendcount()
    {
      using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            foreach (GridViewRow row in GVattend.Rows)
            {
               
                string rollno = (((Label)row.FindControl("lblroll")).Text);
                string attendlecture = (((Label)row.FindControl("lblattend")).Text);

                string query = "SELECT COUNT(*) as attendlecture FROM class8_attend where  rollno ='" + rollno + "' AND present ='p'";
                using (SqlCommand cmd = new SqlCommand(query, cnn))
                {
                    cnn.Open();
                    Int32 count = (Int32)cmd.ExecuteScalar();
                    attendlecture = count.ToString();
                    cnn.Close();
                }
                }
            }
        }

Posted 14-Mar-17 20:13pm

ADI@345

Updated 14-Mar-17 22:19pm

Add a Solution

Comments

Graeme_Grant 15-Mar-17 2:34am

The Main data for the GridView is coming from the DB? Wouldn't it be far simpler to make a single call to the DB and let it do the hard work for you rather than 1 to get the main data, then hitting the DB multiple times (one per row) to get the consolidated counts? The app would be far more responsive then.

Also, you should not concat SQL query strings.

Karthik_Mahalingam 15-Mar-17 4:05am

are you taking distinct?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pt1401 · Answer 1 · 2017-03-14T22:19:00

What you've given us as you're desired output is faulty.
The line

CLASS8 A01 Adi 2017 03/15/2017 Checkbox 2

says that there were 2 occurrences of A01/Adi having attended. But there was only one, the other was roll A02.
Also the batch_date columns don't match - your GridView has a single row for two different batch_dates.

So I'm going to assume you want to group results by role and batch_date, andmodify your data slightly to show how to show to get the data you're looking for in a single query, as Graeme suggested above.

Let's assume you have this table:-

USE [Penvro]
GO

/****** Object:  Table [dbo].[Attendance]    Script Date: 15/03/2017 08:03:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Attendance](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[class] [int] NOT NULL,
	[roll] [varchar](50) NOT NULL,
	[name] [varchar](50) NOT NULL,
	[batch_date] [datetime] NOT NULL,
	[present] [char](1) NOT NULL,
 CONSTRAINT [PK_Attendance] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

and you're populating it with this data:-

INSERT INTO Attendance (class, roll, name, batch_date, present) VALUES
(8, 'A01', 'Adi', '15-Mar-2017 11:30:46', 'P'),
(8, 'A01', 'Adi', '15-Mar-2017 11:30:46', 'P'),
(8, 'A03', 'Ram', '15-Mar-2017 11:30:46', 'A')

Then this query will return the results you need

SELECT class, roll, name, batch_date, CASE WHEN present='P' THEN 1 ELSE 0 END AS present_absent, count(*) AS total
FROM Attendance a
GROUP BY class, roll, name, batch_date, a.present

class   roll  name   batch_date              present_absent total
------- ----- ------ ----------------------- -------------- ------
8       A01   Adi    2017-03-15 00:00:00.000 1              2
8       A03   Ram    2017-03-15 00:00:00.000 0              1