Microsoft KB Archive/293873

= PRB: ADO Recordset AddNew and Update Methods Ignore ANSI PADDING OFF Setting =

Article ID: 293873

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q293873



SYMPTOMS
When you update tables in SQL Server 7.0, the AddNew and Update methods of the Microsoft ActiveX Data Objects (ADO) Recordset object ignore the ANSI PADDING OFF setting. These methods do not trim the trailing spaces or blanks, even if the table is created with SET ANSI_PADDING OFF.

In addition, it does not matter if the USE Ansi NULLS, paddings and warnings option is turned on or off in the Data Source Name (DSN).



Steps to Reproduce Behavior
  Create a test table named testpadoff with ANSI_PADDING OFF as follows: SET ANSI_PADDING OFF CREATE TABLE testpadoff (UserName varchar(10))  Create a Standard EXE project in Visual Basic. Add a reference to ActiveX Data Object Library.  Add the following code to the Form_Load event.

Note You must change User ID = and password = to the correct values before you run this code. Make sure that  has the appropriate permissions to perform this operation on the database. Dim adConn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim strName As String

adConn.Open &quot;Data Source=(local);Initial Catalog=Test;User ID=;Password= &quot; ' Inserting through the connection object always removes padding because ' the table was created with ANSI_PADDING off. strSQL = &quot;insert into TestPadOff values('&quot; & &quot;John &quot; & &quot;')&quot; adConn.Execute strSQL

' Inserting through the Recordset object always preserves padding, ' regardless of the 'SET ANSI_PADDING OFF' setting on the Connection object. adConn.Execute &quot;set ansi_padding off&quot; 'rs.cursorlocation = adUseClient rs.Open &quot;select * from TestPadOff&quot;, adConn, adOpenDynamic, adLockOptimistic

rs.AddNew rs.Fields(0).Value = &quot;Mike  &quot; rs.Update

rs.MoveLast strName = rs.Fields(0).Value Debug.Print rs.Fields(0).Value & &quot; &quot; & CStr(Len(strName)) rs.Close adConn.Close

Set rs = Nothing Set adConn = Nothing End Sub  Run the following query in SQL Server Query Analyzer.

<pre class="fixed_text">select '<' + UserName + '>' from testpadoff

The padding is preserved for the record that is added using the Recordset object.</li>  Uncomment the following line: rs.CursorLocation = adUseClient </li> Run the project.</li> Run the following query in SQL Server Query Analyzer:

<pre class="fixed_text">select '<' + UserName + '>' from testpadoff

The padding is not preserved when you use client-side cursor.</li></ol>

<div class="references_section">