Hi All,

I'm new to VC++ and I've just written my first application that calls some stored procedures performing updates and queries.

In the following code, I always get the following exception when I tried to fetch the result from the select statement in the stored proc:
Error
Code meaning = Unknown error 0x800A0E78
Source = ADODB.Recordset
Description = Operation is not allowed when the object is closed.

However, if i change the stored proc so that it just runs a single query, I could have the result fetch without any problem.

Please advise. Thanks a lot.


Here is the stored proc code:
CREATE PROCEDURE test_sp @id int, @name varchar(32), @value int AS
insert into mytable (id, name, value) values (@id, @name, @value)
create table #testing (col1 int, col2 int)
insert into #testing (col1, col2) values (1, 2)
insert into #testing (col1, col2) values (3, 4)
select col1, col2 from #testing
drop table #testing
GO
Here are the VC++ Code:
pCm->CommandText = "test_db..test_sp";
pCm->CommandType = adCmdStoredProc;
pCm->Parameters->Refresh();
vid.vt = VT_I4;
vid.intVal = 2;
pPa1->Type = adInteger;
pPa1->Size = 4;
pPa1->Value = vid;
pCm->Parameters->Append(pPa1);
vname.vt = VT_BSTR;
vname.bstrVal = _bstr_t("Sammy");
pPa->Type = adBSTR;
pPa->Size = strlen("Sammy");
pPa->Direction = adParamInput;
pPa->Value = vname;
pCm->Parameters->Append(pPa);
vvalue.vt = VT_I4;
vvalue.intVal = 3;
pPa2->Type = adInteger;
pPa2->Size = 4;
pPa2->Value = vvalue;
pCm->Parameters->Append(pPa2);
pRsGet = pCm->Execute(NULL, NULL, adCmdStoredProc);

while (!pRsGet->EndOfFile) {
printf("COL ONE [%s]\n", (char *)(_bstr_t) pRsGet->Fields->GetItem("col1")->Value);
printf("COL TWO [%s]\n", (char *) (_bstr_t) pRsGet->Fields->GetItem("col2")->Value);
pRsGet->MoveNext();
}

It looks like I've got around the problem. The recordset retrieved becomes ok after I've put 'SET NO COUNT ON' in the stored procedure.

Please comment if I'm doing the wrong thing.

BTW, I'd like to know if there is any online resource about the common practice of deveplopment of VC++, ADO and SQL Server.

Thanks a lot.