Click here to Skip to main content
15,036,363 members
Please Sign up or sign in to vote.
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
Updated 14-Mar-17 22:19pm
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

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
   
Comments
pt1401 16-Mar-17 16:25pm
   
Confucious he say "Man who ask 'Please reply me soon' who doesn't acknowledge replies soon, not get reply so soon next time..."

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900