简单的工作室

数据提供程序或其他服务返回 E_Fail 状态

时间:2018-03-18 18:07来源:未知 作者:简单的工作室 点击:
程序采用三层架构,服务端是修改delphi7自带的ScktSrvr.exe 中间层,通过ADO组件连接access数据库,今天在调试程序的时候,突然出现这个错误 数据提供程序或其他服务返回 E_Fail 状态,这个错误时在执行查询语句出现的(select * from 表名) 现场回顾,我打开过ACCESS数据

  程序采用三层架构,服务端是修改delphi7自带的ScktSrvr.exe 中间层,通过ADO组件连接access数据库,今天在调试程序的时候,突然出现这个错误 数据提供程序或其他服务返回 E_Fail 状态,这个错误时在执行查询语句出现的(select * from 表名)

  现场回顾,我打开过ACCESS数据库,直接Insert into 表,开启了宏,但是插入出错过,怀疑access数据库破坏掉了?或者数据库引擎有问题了,或者在插入那张表的时候插入了一些异常数据导致的,select 语句出现了问题。删除该表所有数据,再次查询,不报错了,怀疑数据库里数据有异常类型,跟查询语句不匹配

主要查询语句如下

cGetEmpCardRecord='select ''%1:s'' as WorkOn, ''%2:s'' as WorkOff, ''%3:s'' as Week,''%4:s'' as CardDay, kq_empcrdtm.recordid, '
     +'pb_employee.DepartID,EmpImage,pb_depart.DepartName,'
     +'kq_empcrdtm.sysno,kq_empcrdtm.serial,empid,empname,cdatetime,kq_empcrdtm.picturePath, '
     +'kq_empcrdtm.devicesysid, '
     +' recordtype, '
     +' switch(recordtype=''0'',''打卡'','
     +'recordtype=''1'',''按键'','
     +'recordtype=''2'', ''补卡'','
     +'recordtype=''3'',''修改'','
     +'recordtype=''9'',''错误''), '
     +' kq_empcrdtm.inorout as Ainorout,  '   //2010-03-13 新增进门刷卡 出门刷卡标识 0 进门 1 出门
     +' switch(inorout=''0'',''进门刷卡'','
     +'inorout=''1'',''出门刷卡'', '
     +'inorout=''2'',''异常刷卡''), '
     +' kq_empcrdtm.operatorid '
     +' from ((kq_empcrdtm  '
     +' left join pb_employee on kq_empcrdtm.sysno=pb_employee.EmpID) '
     +' left join pb_depart on pb_employee.DepartID=pb_depart.LevelID) where kq_empcrdtm.AccountMail=''%0:s'' '
     + 'and kq_empcrdtm.recordid in(select max(recordid) from kq_empcrdtm where kq_empcrdtm.AccountMail=''%0:s'' and %5:s group by sysno)  '
     + 'and pb_depart.AccountMail=''%0:s'' and %5:s ';

----------------------------------------------------------------------------------

switch(inorout=''0'',''进门刷卡'','
+'inorout=''1'',''出门刷卡'', '
+'inorout=''2'',''异常刷卡''), '
当inorout 有具体数值 0 或者1 等时候,就出现这个错误,否则能正常执行。

后来直接在ACCESS数据库执行查询语句,发现inorout 是 '错误' 字段返回(虽然运行时没报错)

仔细看了下字段inorout是整形字段,而我switch 用了字符串字段 所以转化貌似出现错误了,把inorout字段改成文本类型 就正常了。还是数据库表字段类型不匹配导致的!



现在数据库改成SQL2008 SQL语句在查询分析器里运行正常,虽然时间有点长,用了3分钟多,但是十六号的数据查询就报错,其他的日期查询正常的,现在我也不知道为什么了,又变成“查询超时已过期”,开始的时候是出现这个提示的,后来我改进SQL语句,这个错误提示木有了,查询的数据记录不多就100多条的,无语......

具体的SQL语句如下,看来要好好研究研究,额

select a.WorkOn, a.WorkOff, '五 ' as Week,'18-03-16 ' as CardDay, a.recordid, pb_employee.DepartID,pb_employee.EmpImage,pb_depart.DepartName, ' ' as AllCardRecord1,  dbo.fun_GetEmpcrdtm(a.sysno,a.accountmail,convert(varchar(21),a.cdatetime,23)) as AllCardRecord, a.sysno ,a.serial,pb_employee.empid,pb_employee.empname,convert(varchar(21),a.cdatetime,108) as cdatetime, convert(varchar(21),b.cdatetime,108) as cdatetime1, convert(varchar(21),(b.cdatetime - a.cdatetime),108) as WorkOnTime,  CAST(DATEPART(hh,convert(varchar(21),(b.cdatetime - a.cdatetime),108)) AS INT)  + cast(CAST(DATEPART(n,convert(varchar(21),(b.cdatetime - a.cdatetime),108)) AS float)/60 as decimal(8,2)) as WorkHour,  CAST(DATEPART(hh,convert(varchar(21),(b.cdatetime - a.cdatetime),108)) AS INT)*60  + CAST(DATEPART(n,convert(varchar(21),(b.cdatetime - a.cdatetime),108)) AS int) as WorkMinute, case when (a.Islater =1) or (b.IsLater=1) then 1 else 0 end as LaterCount,case when (a.Signoutearly=1) or (b.Signoutearly=1) then 1 else 0 end as Signoutearly,case when (a.Absenteeism=1) or (b.Absenteeism=1) then 1 else 0 end as Absenteeism,a.picturePath, a.devicesysid, a.RulesName, case a.recordtype  when '0' then '电脑打卡'  when '1' then '科密终端'  when '2' then '补卡'  when '3' then '修改'  when '5' then '智能考勤'  when '9' then '手机考勤'  else a.recordtype   end as Recordtype,   case a.inorout  when 0 then '进门刷卡'when 1 then  '出门刷卡' when 4 then  '智能机器人参考时间' else  '异常刷卡' end as Ainorout,  a.operatorid,a.Islater  from (((kq_empcrdtm as a  left join pb_employee on (a.sysno=pb_employee.EmpID) and (a.accountmail=pb_employee.accountmail) ) left join kq_empcrdtm as b on a.sysno=b.sysno and a.recordid= b.recordid and b.sysno=pb_employee.EmpID and a.cdatetime=b.cdatetime)  left join pb_depart on pb_employee.DepartID=pb_depart.LevelID and pb_employee.Accountmail=pb_depart.Accountmail) where a.AccountMail='123@123.com' and  b.AccountMail='123@123.com' and   pb_employee.AccountMail='123@123.com' and a.RulesName=b.RulesName and   a.recordid in(select min(recordid)  from kq_empcrdtm where kq_empcrdtm.AccountMail='123@123.com'    and kq_empcrdtm.cdatetime  between (' 2018-03-16') and  ('2018-03-16 ' +(  select ISnull(min(distinct case when a.WorkOff ='休息' then '23:59:58'  when a.workoff='' then '23:59:58' else a.WorkOff end   ),'23:59:59' )   from (((kq_empcrdtm as a  left join pb_employee on (a.sysno=pb_employee.EmpID) and (a.accountmail=pb_employee.accountmail)) left join kq_empcrdtm as b on a.sysno=b.sysno and a.recordid= b.recordid and b.sysno=pb_employee.EmpID and a.cdatetime=b.cdatetime)left join pb_depart on pb_employee.DepartID=pb_depart.LevelID and pb_employee.Accountmail=pb_depart.Accountmail) where a.AccountMail='123@123.com' and  b.AccountMail='123@123.com' and   pb_employee.AccountMail= '123@123.com' and a.RulesName=b.RulesName and a.cdatetime  between (' 2018-03-16') and  ('2018-03-16 23:58:33') ))   group by sysno,RulesName )  and   b.recordid in(select max(recordid) from kq_empcrdtm where  kq_empcrdtm.AccountMail='123@123.com' and  kq_empcrdtm.cdatetime  between (' 2018-03-16') and  ('2018-03-16 23:58:33') group by sysno,RulesName ) and a.AccountMail='123@123.com' and a.cdatetime  between (' 2018-03-16') and  ('2018-03-16 23:58:33')



(责任编辑:简单的工作室)
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 验证码:点击我更换图片
栏目列表
推荐内容