PRB: SQL Server Stores One-Character String with Empty String (177722)

MORE INFORMATION

Besides steps to reproduce the behavior, the following sample code provides a workaround by passing the SQL INSERT statement to add a new record. Command1 demonstrates the problem. Command2 provides a workaround.

Task One: Create Test Table and Stored Procedure

For testing purpose, table TestTbl, is created in the Pubs database. To create a table in SQL Server, select the Pubs database, place the following Create Table T-SQL in the SQL window of ISQL/W and execute it:

      CREATE TABLE TestTbl
             (ID VarChar(20), Comment Text NULL)
      CREATE UNIQUE INDEX IDIndex ON TestTbl(ID)
				

Task Two: Build the Visual Basic Code

1. Start a new project in Visual Basic and choose "Standard EXE". Form1 is created by default.
2. From the Project menu, select References, and then place a check next to Microsoft DAO 3.5 Object Library.
3. Add 2 CommandButtons, Command1, and Command2, to Form1.
4. Paste the following code in the General Declarations section of Form1:

      Option Explicit
      Dim wk As Workspace
      Dim cn As Connection
      Dim rs As Recordset
   
      Sub command1_Click()
         rs.AddNew
         rs("ID") = Now
         rs("Comment") = "Z"
         rs.Update
         rs.Move 0
         rs.Bookmark = rs.LastModified
         Debug.Print "IsEmpty() Returns: " & IsEmpty(rs("Comment"))
         Debug.Print rs("Comment")
      End Sub
   
      Private Sub Command2_Click()
         Dim strSQL As String
         strSQL = "Insert Into TestTbl(ID, Comment) _
         Values('" & Now & "', 'Z')"
         cn.Execute strSQL, dbExecDirect
         rs.Requery
         rs.MoveLast
         Debug.Print "IsEmpty() Returns: " & IsEmpty(rs("Comment"))
         Debug.Print rs("Comment")
      End Sub
   
      Private Sub Form_Load()
         Dim strConnect As String
         Set wk = CreateWorkspace("MyWrk", "", "", dbUseODBC)
         strConnect = "ODBC;DRIVER={SQL Server}; _
         SERVER=MyServer;DATABASE=pubs;UID=<username>;PWD=<strong password>;"
         Set cn = wk.OpenConnection("", dbDriverNoPrompt, True, strConnect)
         Set rs = cn.OpenRecordset _
         ("Select ID, Comment From TestTbl", dbOpenDynaset, , 3)
      End Sub
   
      Private Sub Form_Unload(Cancel As Integer)
         rs.Close
         cn.Close
      End Sub
   						

5. Note that you should change the SERVER, UID, and PWD parameters in the connection string.
6. Start the program or press the F5 key.