Databases
StackOverflow2013
Postsdbo
POADO Command Object Records Affected broken when moving to SQL Server 2008
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POADO Command Object Records Affected broken when moving to SQL Server 2008
    text
    copied!<p>My company is in the process of moving a database from SQL 2000 to SQL 2008. I am in the process of testing some of our legacy applications and came across a VB6 app that is running into a bug. The code is attempting to run a simple update for a record and check that the Execute method has affected more then one row using the RecordsAffected parameter.</p> <p>The VB6 code</p> <pre class="lang-vb prettyprint-override"><code>cmd.ActiveConnection = gcnADODE cmd.CommandType = adCmdStoredProc cmd.CommandText = "Wendy_OffshoreQC_UpdateQCRecord" Set QCID = cmd.CreateParameter("@QCId", adInteger, adParamInput) QCID.value = 1 cmd.Parameters.Append QCID Set QCBatchFailurePercentage = cmd.CreateParameter("@QCBatchFailurePercentage", adDecimal, adParamInput) QCBatchFailurePercentage.Precision = 18 QCBatchFailurePercentage.NumericScale = 2 QCBatchFailurePercentage.value = 1.11 cmd.Parameters.Append QCBatchFailurePercentage Set QCBatchCompleteDate = cmd.CreateParameter("@QCBatchCompleteDate", adDate, adParamInput) QCBatchCompleteDate.value = Format(Now, "yyyy-mm-dd hh:mm:ss") cmd.Parameters.Append QCBatchCompleteDate cmd.Execute numRecordsUpdate success = numRecordsUpdate &gt; 0 </code></pre> <p>The stored procedure create code</p> <pre class="lang-sql prettyprint-override"><code>ALTER proc [dbo].[Wendy_OffshoreQC_UpdateQCRecord] @QCId int ,@QCBatchFailurePercentage decimal (18,2) ,@QCBatchCompleteDate smalldatetime AS UPDATEtblQC SET QCBatchFailurePercentage=@QCBatchFailurePercentage ,QCBatchCompleteDate=@QCBatchCompleteDate WHERE QCID=@QCId </code></pre> <p>I'm not sure which the ADO connection ends up using. The outside provider is the SQL Server OLE DB provider, but in the extended properties the drive says the SQL Server. I'm going to try fixing some of the code to be consistent with the driver that is used.</p> <pre class="lang-none prettyprint-override"><code>Provider=SQLOLEDB.1;Password=1234;Extended Properties="driver={SQL Server};uid=username;database=db; server=server";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096; Workstation ID=PICASWS186;Use Encryption for Data=False;Tag with column collation when possible=False </code></pre> <p>However <code>numRecordsUpdate</code> always returns -1 instead of 1 like it did on SQL 2000. I know I can change the stored procedure to return <code>@@rowcount</code> or just remove the check for success, but there are other parts of the code with this pattern that are having a similar issue.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload