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

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

1. Create a test table named testpadoff with ANSI_PADDING OFF as follows:

   SET ANSI_PADDING OFF
   CREATE TABLE testpadoff
   (UserName varchar(10))
   					

2. Create a Standard EXE project in Visual Basic.
3. Add a reference to ActiveX Data Object Library.
4. Add the following code to the Form_Load event.
   
   Note You must change User ID =<UID> and password =<strong password> to the correct values before you run this code. Make sure that <UID> 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 "Data Source=(local);Initial Catalog=Test;User ID=<UID>;Password=<strong password>"
   '  Inserting through the connection object always removes padding because
   '  the table was created with ANSI_PADDING off. 
   strSQL = "insert into TestPadOff values('" & "John  " & "')"
   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 "set ansi_padding off"
   'rs.cursorlocation = adUseClient
   rs.Open "select * from TestPadOff", adConn, adOpenDynamic, adLockOptimistic
   
   rs.AddNew
   rs.Fields(0).Value = "Mike   "
   rs.Update
   
   rs.MoveLast
   strName = rs.Fields(0).Value
   Debug.Print rs.Fields(0).Value & " " & CStr(Len(strName))
   rs.Close
   adConn.Close
   
   Set rs = Nothing
   Set adConn = Nothing
   End Sub
   					

5. Run the following query in SQL Server Query Analyzer.

   select '<' + UserName + '>' from testpadoff
   							

   The padding is preserved for the record that is added using the Recordset object.
6. Uncomment the following line:

   rs.CursorLocation = adUseClient
   					

7. Run the project.
8. Run the following query in SQL Server Query Analyzer:

   select '<' + UserName + '>' from testpadoff
   							

   The padding is not preserved when you use client-side cursor.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: