Microsoft KB Archive/177722

= PRB: SQL Server Stores One-Character String with Empty String =

Article ID: 177722

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q177722



SYMPTOMS
In Visual Basic 5.0 and higher using ODBC Direct, when a record is added/edited with a one-character string to a Text field of a SQL Server table, the one-character string is replaced with an empty string.



RESOLUTION
Instead of using AddNew or Edit method, use the SQL statement INSERT INTO or UPDATE directly.



STATUS
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



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
 Start a new project in Visual Basic and choose "Standard EXE". Form1 is created by default. From the Project menu, select References, and then place a check next to Microsoft DAO 3.5 Object Library. Add 2 CommandButtons, Command1, and Command2, to Form1.  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= ;PWD= ;" 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  Note that you should change the SERVER, UID, and PWD parameters in the connection string. Start the program or press the F5 key.</li></ol>

Additional query words: sqlserver odbcdirect kbODBC kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

Keywords: kbprb kbpending KB177722

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.