紧急求助,用ADO,_CommandPtr和_RecordsetPtr调用存储过程的问题.在线等待.

[f_d_0]

下面是用ADO调用存储过程的代码片段,不知为什么执行到exexute这个函数的时候就报错,请高手帮忙看一下,不胜感激.

_CommandPtr comm;
_RecordsetPtr rs;

HRESULT hr = comm.CreateInstance(__uuidof(Command));
if(FAILED(hr))
return -1;
comm->Parameters->Append(comm->CreateParameter("@Type",
adInteger,adParamInput,4,(long)Type));
comm->Parameters->Append(comm->CreateParameter("@ProId",
adInteger,adParamInput,4,(long)proId));
comm->Parameters->Append(comm->CreateParameter("@Date",
adVarChar,adParamInput,10,_variant_t(dateString)));
comm->Parameters->Append(comm->CreateParameter("@Time",
adVarChar,adParamInput,10,_variant_t(timeString)));
comm->Parameters->Append(comm->CreateParameter("@PType",
adInteger,adParamInput,4,(long)pType));
comm->Parameters->Append(comm->CreateParameter("@Org",
adInteger,adParamInput,4,(long)org));
comm->Parameters->Append(comm->CreateParameter("@Dtn",
adInteger,adParamInput,4,(long)dtn));
comm->Parameters->Append(comm->CreateParameter("@FareValue",
adInteger,adParamOutput,4,(long)fare));

comm->CommandText = _bstr_t("spComputePrice");
comm->ActiveConnection = DBLink->GetConnection();//返回正确的数据库连接
comm->CommandType = adCmdStoredProc;

comm->Execute(NULL,NULL,adCmdStoredProc);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~执行到这里就报错了.

fare = comm->Parameters->GetItem("FareValue")->GetValue();



然后我就准备采用第二种方法,用_RecordsetPtr

下面是用SQL语句执行存储过程的代码片段,执行能成功,但是不知道怎么取得输出参数和返回的结果呢?请高手指教
CString sSQL;
_RecordsetPtr m_pRecordset;

sSQL.Format("declare @Type int\
declare @ProId int\
declare @Day varchar(10)\
declare @Time varchar(10)\
declare @PType int\
declare @Org int\
declare @Dtn int\
declare @FareValue int\
set @Type=%d\
set @ProId=%d\
set @Day='%s'\
set @Time='%s '\
set @PType=%d\
set @Org=%d\
set @Dtn=%d\
set @FareValue=%d\
exec spComputePrice\
@Type,@ProId,\
@Day,@Time,@PType,\
@Org,@Dtn,@FareValue output",\
iType,proId,dateString,
timeString,pType,org,dtn,fare);

HRESULT hr;
try
{
if(m_pRecordset->State)
m_pRecordset->Close();

m_pRecordset->CursorLocation = adUseServer;
hr = m_pRecordset->Open(_bstr_t(strSQL),
_variant_t((IDispatch*)m_pConnection, true),
adOpenStatic, adLockOptimistic, adCmdText);
if( FAILED(hr) )
{
OutMsg("Open Recordset Error!");
return NULL;
}
}
catch(_com_error &e)
{
OutMsg(e.ErrorMessage());
OutMsg( LPCTSTR(e.Description()) );
return NULL;
}

fare = m_pRecordset->Fields->GetItem(_variant_t((long)0))->Value;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~这里报错,去不到存储过程返回的结果.

请教高手这上面的两个问题怎么解决.我殷切期待.谢谢.

[zaodt]

(long)proId

改为这个，试试看

_variant_t var;

var.ChangeType(VT_I4);
var.intVal = proId;

然后把 CreateParameter 函数的最后一个参数改为 var

[zaodt]

这里有别人的代码，我试过，正确运行，你参考一下。

_ConnectionPtr m_pConnection;
_CommandPtr m_pCommand;
.cpp中在函数中执行
//建立ado连接
HRESULT hr;
hr=m_pConnection.CreateInstance(__uuidof(Connection));
try
{
if(SUCCEEDED(hr))
{
hr=m_pConnection->Open(_bstr_t(L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Viper;Data Source=Viper"),_bstr_t (L"sa"),_bstr_t (L""),adModeUnknown);
}
}
catch(_com_error & err)
{
AfxMessageBox(err.Description(),MB_OK,0);
AfxMessageBox(err.ErrorMessage(),MB_OK,0);
AfxMessageBox("无法连接SQL SERVER 服务器，程序将退出。请检查网络设备",MB_OK,0);
exit(0);
}

//执行储存过程
CString cvar1,cvar2;
int cvar3;
cvar1="ddd";
cvar2="";
cvar3=0;
try
{
m_pCommand.CreateInstance(__uuidof(Command));
m_pCommand->ActiveConnection=app->m_pConnection;
m_pCommand->CommandType=adCmdStoredProc;
m_pCommand->CommandText=_bstr_t("pr_zs_dzdy");

_variant_t vvar1,vvar2,vvar3;
vvar1=_variant_t(_bstr_t(cvar1));
vvar2=_variant_t(_bstr_t(cvar2));
vvar3=_variant_t(cvar3);
_ParameterPtr mp_var1,mp_var2,mp_var3;
mp_var1.CreateInstance(__uuidof(Parameter));
mp_var2.CreateInstance(__uuidof(Parameter));
mp_var3.CreateInstance(__uuidof(Parameter));

mp_var1=m_pCommand->CreateParameter
(
_bstr_t("var1"),
adVarChar,
adParamInput,
3,
vvar1
);
m_pCommand->Parameters->Append(mp_var1);

mp_var2=m_pCommand->CreateParameter
(
_bstr_t("var2"),
adVarChar,
adParamOutput,
3,
vvar2
);
m_pCommand->Parameters->Append(mp_var2);

mp_var3=m_pCommand->CreateParameter
(
_bstr_t("var3"),
adIntger,
adParamOutput,
9,
vvar3
);
m_pCommand->Parameters->Append(mp_var3);


_variant_t vNull;
vNull.vt=VT_ERROR;
vNull.scode=DISP_E_PARAMNOTFOUND;
m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);
cvar2=mp_var2->Value.bstrVal;
cvar3=mp_var3->Value;
}
catch(_com_error &error)
{
MessageBox(error.ErrorMessage(),"ADO错误!");
MessageBox(error.Description(),"ADO错误!");
}

[zaodt]

楼主可以先建立一个很简单的，只带一个参数的存储过程，实验一下。

[f_d_0]

to:zaodt
OK,我去试试

[f_d_0]

to:zaodt
问题还是一样

[尘雨]

建议还是使用command来执行带参数的存储过程，注意参数的顺序和存储过程定义中的参数顺序要一致
另外返回值取得，也是使用参数来取得，不过这个参数必须是被comm->Parameters集合的第一个被添加进去的，否则会出错

[尘雨]

如果可以请贴出存储过程的参数声明，以供调试测试

[尘雨]

呵呵

[小海贼]

在存储过程中开始处加上
set nocount on
结尾处加上set nocount off
ado 就是这样，会接受存储过程的行影响