Microsoft KB Archive/187945

= How To Pass Text Fields to a Stored Procedure Using RDO 2.0 =

Article ID: 187945

Article Last Modified on 7/1/2004

-

APPLIES TO


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

-



This article was previously published under Q187945



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.



Steps to Reproduce
  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

  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

  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)

  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.

Note You must change UID and PWD to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. 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=;UID= ;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

 Click the CommandButton.  Executing "select text1 from textbloat" using ISQL you will get the following output that shows the concatenation: <pre class="fixed_text">     text1 -     ABCDE ABCDEABCDE ABCDEABCDEABCDE

</li>  Remove the commented lines from the above code and do a "select text1 from textbloat" again using ISQL. You will get the expected output: <pre class="fixed_text">     text1 -     ABCDE ABCDE ABCDE

</li>  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

</li></ol>

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.

<div class="references_section">