DOCUMENT:Q187945  09-JAN-2000  [vbwin]
TITLE   :HOWTO: Pass Text Fields to a Stored Procedure Using RDO 2.0
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:
KEYWORDS:kbGrpDSVBDB

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

Repeatedly passing a text field to a SQL server stored procedure using RDO 2.0
will result in concatenation of the newly-passed text field to the earlier one.
This also applies to executing the stored procedure through User Connection
Object.

MORE INFORMATION
================

Steps to Reproduce
------------------

1. Create a table using the following script in the pubs sample database (SQL
   Server):

         CREATE TABLE dbo.textbloat (
           ID int NOT NULL ,
           text1 text NULL
         )
         GO

2. Create a procedure using the following script:

         CREATE PROCEDURE p_Edit
           @ID int,
           @Desc Text AS
           UPDATE textbloat SET Text1 = @Desc WHERE ID = @ID
         GO

3. Add three dummy records into the table using the following script:

         insert into textbloat values (1,null)
         insert into textbloat values (2,null)
         insert into textbloat values (3,null)

4. Create a new Standard EXE project. Form1 is created by default. Add a
   CommandButton to the form, and name it Command1. Paste the following code
   into its click event.

        Private Sub Command1_Click()
         Dim rdoenv As RDO.rdoEnvironment
         Dim rdoconn As RDO.rdoConnection
         Dim rdq As RDO.rdoQuery
           Set rdoenv = rdoEnvironments(0)
           rdoenv.CursorDriver = rdUseOdbc
           Set rdoconn = rdoenv.OpenConnection("", rdDriverNoPrompt, False, _
                         "DSN=<Your DSN Name>;UID=sa;PWD=;database=pubs")
           Set rdq = rdoconn.CreateQuery("", "{Call p_Edit(?,?)}")
           rdq.Prepared = True
           rdq(0).Type = rdTypeINTEGER
           rdq(0).Direction = rdParamInput
           rdq(1).Direction = rdParamInput
           rdq(1).Type = rdTypeLONGVARCHAR
           rdq(0).Value = 1
           'rdq(1).Value = ""
           rdq(1).Value = "ABCDE"
           rdq.Execute
           rdq(0).Value = 2
           'rdq(1).Value = ""
           rdq(1).Value = "ABCDE"
           rdq.Execute
           rdq(0).Value = 3
           'rdq(1).Value = ""
           rdq(1).Value = "ABCDE"
           rdq.Execute
         End Sub

5. Click the CommandButton.

6. Executing "select text1 from textbloat" using ISQL you will get the following
   output that shows the concatenation:

      text1
      -----
      ABCDE
      ABCDEABCDE
      ABCDEABCDEABCDE

7. Remove the commented lines from the above code and do a "select text1 from
   textbloat" again using ISQL. You will get the expected output:

      text1
      -----
      ABCDE
      ABCDE
      ABCDE

8. If you intend to execute the above stored procedure using a UserConnection
   object, you will have to explicitly set the text field parameter to null or
   "" before executing, in the following way:

         Private Sub Command2_Click()
         Dim uc As New UserConnection1
           uc.EstablishConnection
           uc.p_edit 1, "ABCDE"
           uc.rdoQueries.Item("p_edit").rdoParameters("Desc").Value = ""
           uc.p_edit 2, "ABCDE"
           uc.rdoQueries.Item("p_edit").rdoParameters("Desc").Value = ""
           uc.p_edit 3, "ABCDE"
         End Sub

For the above code to work, you will have to add a UserConnection Object to the
Visual Basic project and name it UserConnection. You will also have to add a new
CommandButton to the form named Command2.

REFERENCES
==========

Visual Basic RDO Help

Additional query words: kbDSupport kbDse kbRDO200 kbVBp500 kbVBp kbVBp600 kbNoKeyWord

======================================================================
Keywords          : kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2000.