调查了n久,有点郁闷的,最后在http://www.dbforums.com/delphi-c-etc/631285-problem-executing-stored-proc-vc.html 这篇里面找到解决方案。
VC++使用ADO操作数据库时,当sql语句(或存储过程)中包含Insert,Update,Delete语句时,调用Recordset读取数据的时候,会报Unknown error 0x800A0E78异常。
解决方案:
存储过程开始加:SET NOCOUNT ON
Sql语句最前面加:SET NOCOUNT ON
比如:
TCHAR szSql[2048];
_stprintf(szSql, _T("SET NOCOUNT ON if not exists(select * from HHDevice where DeviceGUID = '%s') \
insert into HHDevice (DeviceGUID, DeviceType, DeviceName, IP, Port, Username, Password, DateCreated, DateModified, CompanyID) \
values('%s', \
%d, \
'%s', \
'%s', \
%d, \
'%s', \
'%s', \
GetDate(),\
GetDate(),\
%d) \
declare @deviceId int \
declare @companyId int \
select @deviceId = DeviceID, @companyId = CompanyID from HHDevice where DeviceGUID = '%s' \
if (@deviceId > 0) \
begin \
insert into HHLogin (ClientType, ClientID, IP, Port, DateCreated, CompanyID) \
values (%d, @deviceId, '%s', %d, GetDate(), @companyId) \
select @@IDENTITY as LoginID \
end \
else \
select -1 as LoginID"), ...)
auto execResult = pDbExec->Execute(szSql);
while(!execResult->adoEOF) //如果没有SET NOCOUNT ON,这里开始就会抛异常
{
...
}