ADO recordset not properly open when update followed by select in Stored Procedure

• richard.smith 47066

  Mr or Mrs. 500

  Points: 513

  More actions

  October 8, 2009 at 9:44 am

  #96000

  When calling a staored procedure from C++ code using ADO, the recordset only seems to behave properly if there has been no update in the stored procedure before the select statement returns the recordset. Here is the SP (abbreviated)

  ------------------------------

  INSERT INTO [CollectionContent]([CollectionID],

  [LinenProductCode], etc...

  SELECT [CollectionID], [LinenProductCode], etc..

  FROM [LBaseArchive].[dbo].[CollectionContent]

  where CollectionID = @collid

  select @Error = @@ERROR

  if @Error <> 0

  begin

  print 'Error transferring content'

  select 2 as Status

  return

  end

  select 0 as Status

  ---------------------------------

  I know that the SP is returning 0 because the insert statement is being performed correctly.

  Here is the ADO code:

  try

  {

  pRecordset->CursorLocation = adUseClient;

  HRESULT hropen = pRecordset->Open ((IDispatch *) pCommand, vtMissing, adOpenForwardOnly,

  adLockOptimistic, adCmdStoredProc);

  _variant_t vData;

  pRecordset->MoveFirst(); // returns error here, even though hropen is = S_OK

  while (!pRecordset->GetadoEOF ())

  {

  vData = pRecordset->GetCollect ("Status");

  nInt = (int) vData.iVal;

  nStatus = nInt;

  cc++;

  pRecordset->MoveNext ();

  }

  pRecordset->Close ();

  }

  catch( _com_error &e )

  {

  PrintComError(e);

  }

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 8, 2009 at 10:52 am

  #1063608

  You might want to read:

  Adjusting Transaction Isolation Levels

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9dfc3c68-9bf0-4896-b3b6-d2f1a601ead1.htm

  And consider adding a BEGIN TRANSACTION - END TRANSACTION for your update statement.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Matt Whitfield

  SSCrazy Eights

  Points: 8107

  More actions

  October 9, 2009 at 4:02 am

  #1063957

  Try putting SET NOCOUNT ON in your proc before the update.

  Atlantis Interactive - SQL Server Tools
  My blog[/url]
  Why I wrote a sql query analyzer clone