• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Getting Varchar Param Value From Stored Proc in SQL 7.0

The output parameter in Stored Proc is defined as Varchar. After executing the stored proc, "@Two", the output parameter contains "" instead of the actual returned value. I am including the source here:

CREATE PROCEDURE TestProc
    @One As Integer,
    @Two As  varchar(20)  Output
As

Select @Two = 'johnny'

VB Class:

Option Explicit

Public sDSN As String
Public sUserID As String
Public sPW As String
Public sStoredProcName As String

Private m_oConnection As ADODB.Connection
Private m_oCommand As New ADODB.Command

Private Sub Class_Initialize()
 
  Set m_oConnection = New ADODB.Connection
  Set m_oCommand = New ADODB.Command
 
  sDSN = ""
  sUserID = ""
  sPW = ""
  sStoredProcName = ""

End Sub

Private Sub Class_Terminate()
 
  Set m_oConnection = Nothing
  Set m_oCommand = Nothing

End Sub

Public Sub InitializeConnection()

  m_oConnection.ConnectionString = "driver={SQL Server};" & _
        "server=JIMAM;uid=johnny;pwd=johnny;database=Test"

  m_oConnection.Open

  m_oCommand.CommandType = adCmdStoredProc
 
End Sub

Public Sub AddParam(ByVal ParamName As String, _
                         ByVal ParamType As DataTypeEnum, _
                         ByVal Direction As ParameterDirectionEnum, _
                         Optional ByVal Value As Variant)

  On Error GoTo ErrorHandler
 
  Dim Param As ADODB.Parameter
 
  Set Param = m_oCommand.CreateParameter(ParamName, ParamType, Direction, , Value)
 
  m_oCommand.Parameters.Append Param
 
  Set Param = Nothing
 
  Exit Sub

ErrorHandler:
    Set Param = Nothing
    Err.Raise Err.Number, , Err.Description
    Exit Sub
   
End Sub
                         
Public Sub AssignParamValue(ByVal ParamName As String, _
                                 ByVal Value As Variant)

  m_oCommand(ParamName) = Value
 
End Sub
                         
Public Function GetParamValue(ByVal ParamName As String) As Variant

  GetParamValue = m_oCommand(ParamName)

End Function
                         
Public Sub ExecuteProc()
 
  m_oCommand.CommandText = sStoredProcName
  m_oCommand.Execute
 
End Sub

Form Code:

 Private Sub Command1_Click()
  Dim x As New ABSQLStoredProc
 
  x.InitializeConnection
 
  x.sStoredProcName = "TestProc"
 
  x.AddParam "@One", adInteger, adParamInput, 1
  x.AddParam "@Two", adBSTR, adParamOutput
 
  x.ExecuteProc
 
  Command1.Caption = CStr(x.GetParamValue("@Two"))
 
  Set x = Nothing
 
End Sub

Please help.

Thanks,
Johnny
0
johnny_imam
Asked:
johnny_imam
  • 3
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Try adVarChar instead of adBSTR.
0
 
johnny_imamAuthor Commented:
When I define it as adVarChar, this exception is raised:

"The application has improperly defined a Parameter object."
0
 
Brendt HessSenior DBACommented:
Use the Refresh method to see what data types that ADO thinks the parameters are.

Also, you should specify the size of the parameters.  Modify your code thus:


Public Sub AddParam(ByVal ParamName As String, _
                         ByVal ParamType As DataTypeEnum, _
                         ByVal Direction As ParameterDirectionEnum, _
                         ByVal Size as Integer, _
                         Optional ByVal Value As Variant)

  On Error GoTo ErrorHandler
   
  Dim Param As ADODB.Parameter
   
  Set Param = m_oCommand.CreateParameter(ParamName, ParamType, Direction, Size, Value)
   
  m_oCommand.Parameters.Append Param
   
  Set Param = Nothing
   
  Exit Sub

....

  x.AddParam "@One", adInteger, adParamInput, 8, 1
  x.AddParam "@Two", adVarChar, adParamOutput, 20

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johnny_imamAuthor Commented:
bhess1,

Specifying the size did it...please go ahead answer the question so I can assign the grade.

Thanks,
Johnny
0
 
Brendt HessSenior DBACommented:
You can accept a comment as an answer - just check the 'accept comment as answer' when reviewing the question.
0
 
johnny_imamAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now