SqlDataAdapter Fill and FillSchema errors

[adriankitch]

I have a question regarding the SQLDataAdapter fill method. I am currently trying to process records in a datatable retrieved from SQL server. The problems are an ‘OutofMemoryException’ when the DataAdapter is filling the DataTable or a ‘System error at the FillSchema method. The code below is used as part of a batch loop so is reused. The Fill error sometimes occurs the first time this code is used and always when the code is used again for the next set of data.
As you can see I try to release the memory of the previous variables before they are reused, including gc but this doesn’t fix the problem.

DataTable* getData4Update(String* query)
{
if(adapter){adapter->Dispose();}
if(dataTbl){dataTbl->Dispose();}
if(cmdBuilder){cmdBuilder->Dispose();}
GC::Collect;
adapter = new SqlDataAdapter();
dataTbl = new DataTable();
adapter->SelectCommand = new SqlCommand();
adapter->SelectCommand->CommandTimeout = 0;
adapter->SelectCommand->Connection = sqlCon;
adapter->SelectCommand->CommandText = query;
adapter->FillSchema(dataTbl, SchemaType::Source);
adapter->Fill(dataTbl);
cmdBuilder = new SqlCommandBuilder(adapter);
return dataTbl;
};

This has been driving me nuts for a while so any suggestion or alternative would be appreciated. I’m using VC++ 2002 if that makes any difference.

Cheers

Adrian