VB数据库中一个难点,请大家帮忙!
我用ADODC+DATAGRID建立了一个ACCESE的连接,数据很顺利的读到了DATAGRID中,但是因为需要还要再次对其内容进行筛选,而且无法在ADODC的SQL命令中添加了,因为SQL的命令已经很多了,再次添家VB会出错退出.有什么办法对保存在DATAGRID中的数据进行筛选或者干脆用满足某一条件就删除掉也行,但是前提是不能对数据库进行写操作,所以临时表的方法也不行.能帮我想想吗?现在发送的SQL命令如下,就是让大家看看这个SQL有多复杂,没办法再用SQL命令去进行筛选了:
strsql1 = "SELECT t_b_Consumer.f_ConsumerId AS 编号,t_b_Consumer.f_ConsumerName AS 姓名,t_b_Group.f_GroupName AS 部门,Dateserial(Datepart('yyyy','" & 考勤开始日期 & "'),Datepart('m','" & 考勤开始日期 & "'),"
strsql1 = strsql1 & " Datepart('d','" & 考勤开始日期 & "')) AS 日期,(SELECT TOP 1 t_d_CardRecord.f_ReadDate"
strsql1 = strsql1 & " FROM t_d_CardRecord"
strsql1 = strsql1 & " WHERE ((t_b_Consumer.f_ConsumerId = t_d_CardRecord.f_ConsumerId"
strsql1 = strsql1 & " AND (Datediff('d','" & 考勤开始日期 & "',[t_d_CardRecord].[f_ReadDate])) = 0)"
strsql1 = strsql1 & " AND ((Datediff('n',' " & 上午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) >=- " & 上午刷卡提前 & ""
strsql1 = strsql1 & " AND (Datediff('n',' " & 上午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) <= " & 上午刷卡延后 & "))) AS 上午刷卡,Iif(Isnull(上午刷卡),'未刷卡',Iif((Datediff('n',' " & 上午上班时间 & "',Timeserial(Datepart('h',上午刷卡),Datepart('n',上午刷卡),Datepart('s',上午刷卡)))) <= " & 上午迟到允许 & ","
strsql1 = strsql1 & " '正常','迟到')) AS 上午考勤结果,(SELECT TOP 1 t_d_CardRecord.f_ReadDate"
strsql1 = strsql1 & " FROM t_d_CardRecord"
strsql1 = strsql1 & " WHERE ((t_b_Consumer.f_ConsumerId = t_d_CardRecord.f_ConsumerId"
strsql1 = strsql1 & " AND (Datediff('d',' " & 考勤开始日期 & "',[t_d_CardRecord].[f_ReadDate])) = 0)"
strsql1 = strsql1 & " AND ((Datediff('n',' " & 下午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) >=- " & 下午刷卡提前 & ""
strsql1 = strsql1 & " AND (Datediff('n',' " & 下午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) <= " & 下午刷卡延后 & "))) AS 下午刷卡,Iif(Isnull(下午刷卡),'未刷卡',Iif((Datediff('n',' " & 下午上班时间 & "',Timeserial(Datepart('h',下午刷卡),Datepart('n',下午刷卡),Datepart('s',下午刷卡)))) <= " & 下午迟到允许 & ","
strsql1 = strsql1 & " '正常','迟到')) AS 下午考勤结果,Iif(上午考勤结果 = '迟到',1 + Iif(下午考勤结果 = '迟到',1,0),"
strsql1 = strsql1 & " Iif(下午考勤结果 = '迟到',1,0)) AS 迟到次数,Iif(上午考勤结果 = '未刷卡',1 + Iif(下午考勤结果 = '未刷卡',1,0),"
strsql1 = strsql1 & " Iif(下午考勤结果 = '未刷卡',1,0)) AS 未刷卡次数,迟到次数 + 未刷卡次数 AS 违纪总数"
strsql1 = strsql1 & " FROM t_b_Consumer"
strsql1 = strsql1 & " LEFT JOIN t_b_Group"
strsql1 = strsql1 & " ON t_b_Consumer.f_GroupId = t_b_Group.f_GroupId"
strsql1 = strsql1 & " WHERE (((t_b_Consumer.f_AttendEnabled) = 1))"
strsql1 = strsql1 & " AND t_b_Consumer.f_GroupId = Iif( " & 部门考勤 & " = 0,t_b_Group.f_GroupId, " & 部门考勤 & ")"
If 日期差 > 0 Then
For i = 1 To 日期差 Step 1
现在日期 = 现在日期 + 1
strsql1 = strsql1 & "UNION all (SELECT t_b_Consumer.f_ConsumerId AS 编号,t_b_Consumer.f_ConsumerName AS 姓名,t_b_Group.f_GroupName AS 部门,Dateserial(Datepart('yyyy','" & 现在日期 & "'),Datepart('m','" & 现在日期 & "'),"
strsql1 = strsql1 & " Datepart('d','" & 现在日期 & "')) AS 日期,(SELECT TOP 1 t_d_CardRecord.f_ReadDate"
strsql1 = strsql1 & " FROM t_d_CardRecord"
strsql1 = strsql1 & " WHERE ((t_b_Consumer.f_ConsumerId = t_d_CardRecord.f_ConsumerId"
strsql1 = strsql1 & " AND (Datediff('d','" & 现在日期 & "',[t_d_CardRecord].[f_ReadDate])) = 0)"
strsql1 = strsql1 & " AND ((Datediff('n',' " & 上午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) >=- " & 上午刷卡提前 & ""
strsql1 = strsql1 & " AND (Datediff('n',' " & 上午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) <= " & 上午刷卡延后 & "))) AS 上午刷卡,Iif(Isnull(上午刷卡),'未刷卡',Iif((Datediff('n',' " & 上午上班时间 & "',Timeserial(Datepart('h',上午刷卡),Datepart('n',上午刷卡),Datepart('s',上午刷卡)))) <= " & 上午迟到允许 & ","
strsql1 = strsql1 & " '正常','迟到')) AS 上午考勤结果,(SELECT TOP 1 t_d_CardRecord.f_ReadDate"
strsql1 = strsql1 & " FROM t_d_CardRecord"
strsql1 = strsql1 & " WHERE ((t_b_Consumer.f_ConsumerId = t_d_CardRecord.f_ConsumerId"
strsql1 = strsql1 & " AND (Datediff('d',' " & 现在日期 & "',[t_d_CardRecord].[f_ReadDate])) = 0)"
strsql1 = strsql1 & " AND ((Datediff('n',' " & 下午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) >=- " & 下午刷卡提前 & ""
strsql1 = strsql1 & " AND (Datediff('n',' " & 下午上班时间 & "',Timeserial(Datepart('h',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('n',[t_d_CardRecord].[f_ReadDate]),"
strsql1 = strsql1 & " Datepart('s',[t_d_CardRecord].[f_ReadDate])))) <= " & 下午刷卡延后 & "))) AS 下午刷卡,Iif(Isnull(下午刷卡),'未刷卡',Iif((Datediff('n',' " & 下午上班时间 & "',Timeserial(Datepart('h',下午刷卡),Datepart('n',下午刷卡),Datepart('s',下午刷卡)))) <= " & 下午迟到允许 & ","
strsql1 = strsql1 & " '正常','迟到')) AS 下午考勤结果,Iif(上午考勤结果 = '迟到',1 + Iif(下午考勤结果 = '迟到',1,0),"
strsql1 = strsql1 & " Iif(下午考勤结果 = '迟到',1,0)) AS 迟到次数,Iif(上午考勤结果 = '未刷卡',1 + Iif(下午考勤结果 = '未刷卡',1,0),"
strsql1 = strsql1 & " Iif(下午考勤结果 = '未刷卡',1,0)) AS 未刷卡次数,迟到次数 + 未刷卡次数 AS 违纪总数"
strsql1 = strsql1 & " FROM t_b_Consumer"
strsql1 = strsql1 & " LEFT JOIN t_b_Group"
strsql1 = strsql1 & " ON t_b_Consumer.f_GroupId = t_b_Group.f_GroupId"
strsql1 = strsql1 & " WHERE (((t_b_Consumer.f_AttendEnabled) = 1))"
strsql1 = strsql1 & " AND t_b_Consumer.f_GroupId = Iif( " & 部门考勤 & " = 0,t_b_Group.f_GroupId, " & 部门考勤 & "))"
