Connection is busy ...

Hi all,

I use VC++ 6 and MFC, with SQL Express 2005.

source code sample :

CRecordset *rec1 = new CRecordset(machine->pDB);
rec1->Open(...,szSQL,...);
while(!rec1->IsEOF())
{
CDBVariant var1,var2;
rec1->GetFieldValue("COL1",var1);
rec1->GetFieldValue("COL2",var2);

int val1 = GET_INDEX(var1);
int val2 = GET_INDEX(var2);
var1.Clear();
var2.Clear();

CRecordset *rec2 = new CRecordset(machine->pDB);
szSQL.Format("SELECT COL3,COL4 FROM TB1 WHERE COL3 = %i AND COL4 = %i",val1,val2);
rec2->Open(...,szSQL,...);
while(rec2->IsEOF())
{
//Something
rec2->MoveNext();
}
rec2->Close();
delete rec2;
rec1->MoveNext();
}
rec1->Close();
delete rec1;
On the red line, i catch a CBDException "Connection is busy with results for another command"

I think, I can't use the same database connection with severals Recordset simultaneous but why and it's normal, what is wrong in my source code or in the database server configuration ?

Please help me ...

By default, the connection you made to server doesn't support MARS(Multiple Active ResultSet). Which means, that you can only open one result set against server per connection. You need to close one result set before you open another result set.

But you can make this work my turning on MARS. You can refer to the following blog for information on how to turn on MARS in your connection string. Please note that you need to use SNAC to be able to use MARS feature.

http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Please let me know if you need more help on this.