如何从数据库中计算每条记录并显示gridview中每条记录的计数值 [英] How to count each record from database and display count value with each record in gridview
问题描述
我试图计算数据库中的记录数并显示每个gridview记录的计数值。
Ex： -
数据库记录
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
和显示在gridview： -
CLASS ROLL NAME BATCH DATE PRESENT / DENT参加演讲
CLASS8 A01 Adi 2017 03/15/2017 Checkbox 2
CLASS8 A02 Ram 2017 03/15/2017复选框0
请尽快回复我..
我无法显示上课讲座价值..
我尝试过：
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();
}
}
}
}
推荐答案
你给我们的东西因为你想要的输出是错误的。
线
What you've given us as you're desired output is faulty.
The line
CLASS8 A01 Adi 2017 03/15/2017 Checkbox 2
表示A01 / Adi有2次出席。但只有一个，另一个是滚动A02。
此外，batch_date列也不匹配 - 您的GridView有两行不同的batch_dates。
因此，我假设您希望按角色和batch_date对结果进行分组，并稍微修改您的数据以显示如何显示以在单个查询中获取您正在查找的数据，如Graeme上面建议的那样。
我们假设你有这张表： -
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
这篇关于如何从数据库中计算每条记录并显示gridview中每条记录的计数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
