HOWTO: Pass Text Fields to a Stored Procedure Using RDO 2.0

ID: Q187945

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

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.


     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

Click the CommandButton.

Executing "select text1 from textbloat" using ISQL you will get the following output that shows the concatenation:
```
      text1
      -----
      ABCDE
      ABCDEABCDE
      ABCDEABCDEABCDE
 
```

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
 
```

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 : kbGrpVBDB
Version :
Platform : WINDOWS
Issue type : kbhowto