========================
table_test表的结构如下:
IDx int
aaa nvarchar(50)
bbb nvarchar(50)表中数据如下:
IDx aaa bbb
1 a1 b1
2 a2 b2========================
存储过程如下:
@ID1 int = 1,
@ID2 int = 2 ASBEGIN
declare @select1 table
(a nvarchar(50),b nvarchar(50) );
insert into @select1(a,b)
select aaa,bbb from cletgem_test;
select * from @select1;END
==============================
ADO调用这个存储过程的代码如下: inline void TESTHR(HRESULT x) { if FAILED(x) _com_issue_error(x);} _RecordsetPtr pRst = NULL;
_CommandPtr pCmd = NULL;
_ParameterPtr pPrm1 = NULL;
_ParameterPtr pPrm2 = NULL;
_ConnectionPtr pConn = NULL; HRESULT hr = S_OK;
VARIANT vtID1;
VARIANT vtID2;
vtID1.vt = VT_I4;
vtID2.vt = VT_I4;
vtID1.lVal = 1;
vtID2.lVal = 2; //通过udl文件文件连接数据库
CString strConnect = _T("File Name=");
strConnect += mdbName;
try
{
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
hr = pConn->Open(_bstr_t(LPCTSTR(strConnect)), "", "", adConnectUnspecified);
pConn->CursorLocation = adUseClient; TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->CommandText = strProc;
pCmd->CommandType = adCmdStoredProc;
pPrm1 = pCmd->CreateParameter("@ID1",adInteger,adParamInput,sizeof(long),vtID1);
pPrm2 = pCmd->CreateParameter("@ID2",adInteger,adParamInput,sizeof(long),vtID2);
pCmd->Parameters->Append(pPrm1);
pCmd->Parameters->Append(pPrm2);
pPrm1->Value = vtID1;
pPrm2->Value = vtID2; pCmd->ActiveConnection = pConn; //错误发生处
pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc);
pRst->MoveFirst();
return TRUE;
} catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescritpion(e.Description());
return FALSE;
}
}=================================================================问题:
当调用不含表变量的存储过程时,这段代码工作能够将结果取出,但是,当存储过程中包含表变量(比如上示)时,就不正常了=================================================================为什么呢?怎么办呢?项目被这个问题卡住,咳不出来又咽不下去,快憋死了
table_test表的结构如下:
IDx int
aaa nvarchar(50)
bbb nvarchar(50)表中数据如下:
IDx aaa bbb
1 a1 b1
2 a2 b2========================
存储过程如下:
@ID1 int = 1,
@ID2 int = 2 ASBEGIN
declare @select1 table
(a nvarchar(50),b nvarchar(50) );
insert into @select1(a,b)
select aaa,bbb from cletgem_test;
select * from @select1;END
==============================
ADO调用这个存储过程的代码如下: inline void TESTHR(HRESULT x) { if FAILED(x) _com_issue_error(x);} _RecordsetPtr pRst = NULL;
_CommandPtr pCmd = NULL;
_ParameterPtr pPrm1 = NULL;
_ParameterPtr pPrm2 = NULL;
_ConnectionPtr pConn = NULL; HRESULT hr = S_OK;
VARIANT vtID1;
VARIANT vtID2;
vtID1.vt = VT_I4;
vtID2.vt = VT_I4;
vtID1.lVal = 1;
vtID2.lVal = 2; //通过udl文件文件连接数据库
CString strConnect = _T("File Name=");
strConnect += mdbName;
try
{
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
hr = pConn->Open(_bstr_t(LPCTSTR(strConnect)), "", "", adConnectUnspecified);
pConn->CursorLocation = adUseClient; TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->CommandText = strProc;
pCmd->CommandType = adCmdStoredProc;
pPrm1 = pCmd->CreateParameter("@ID1",adInteger,adParamInput,sizeof(long),vtID1);
pPrm2 = pCmd->CreateParameter("@ID2",adInteger,adParamInput,sizeof(long),vtID2);
pCmd->Parameters->Append(pPrm1);
pCmd->Parameters->Append(pPrm2);
pPrm1->Value = vtID1;
pPrm2->Value = vtID2; pCmd->ActiveConnection = pConn; //错误发生处
pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc);
pRst->MoveFirst();
return TRUE;
} catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescritpion(e.Description());
return FALSE;
}
}=================================================================问题:
当调用不含表变量的存储过程时,这段代码工作能够将结果取出,但是,当存储过程中包含表变量(比如上示)时,就不正常了=================================================================为什么呢?怎么办呢?项目被这个问题卡住,咳不出来又咽不下去,快憋死了
pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc);
没有将记录集返回给pRst
begin
set nocount on
.........
set nocount off
end
试试
使用pRst=pRst.NextRecordSet(NULL),调试看看pRst是否地址为空,如果不是空那么就有下个记录集,移动到下个记录集拿值。
请试试
使用pRst=pRst.NextRecordSet(NULL),调试看看pRst是否地址为空,如果不是空那么就有下个记录集,移动到下个记录集拿值。
请试试我在pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc)后有一句if( adStateOpen == pCmd->State )当失败发生的时候,这个条件总是不能成立
可以用临时表试试看
能具体点吗?就以如上所示为例的话呢?
@ID1int = 1,
@ID2int = 2 ASBEGIN
declare @select1 table
(a nvarchar(50),b nvarchar(50) );
insert into @select1(a,b)
select aaa,bbb from cletgem_test;
select * from @select1;END
--------------
写全点,我看看吧,按照这些代码不能是一个存储过程的,把包括CREATE的代码都写下吧。
create procedure test
@ID1 int = 1,
@ID2 int = 2as
begindeclare @select1 table
(a nvarchar(50),b nvarchar(50) );
insert into @select1(a,b)
select aaa,bbb from table_test;
select * from @select1;end
go
(
@ID1 int = 1,
@ID2 int = 2
)
as
set nocount ondeclare @select1 table (a nvarchar(50),b nvarchar(50) )insert into @select1(a,b) select aaa,bbb from table_testselect * from @select1set nocount off
go--------------------
用RecordSet的Open方法:
pRecordset->Open((_bstr_t)"exec test 1,2",m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdUnknown);
pConn->CursorLocation = adUseClient;这一句有没有问题?我是按照msdn里面的设置做的,有什么问题吗?实在想不出来原因了,已经站在崩溃的边缘了555555555~
m_pConnection->Open((_bstr_t)"Provider=SQLOLEDB;Data Source=机器名; Initial Catalog=数据库名;Integrated Security=SSPI;",_bstr_t(""),_bstr_t(""),adModeUnknown);
//SQL认证
m_pConnection->Open((_bstr_t)"Provider=SQLOLEDB;Data Source=机器名; Database=数据库名;UID=用户名;PWD=密码;",_bstr_t(""),_bstr_t(""),adModeUnknown);
#import "c:\program files\common files\system\ado\msado15.dll"no_namespaces rename("EOF" adoEOF")
如何输出一个表变量?