Very Computer

I have the following proc ( doesn't do much but return result set , return
value, and output parameter):

-- Creates the procedure
alter procedure sp_JobsTest

-- declare three parameter variables

AS

SELECT *
FROM job_title_code

The vb code uses command object to load and execute. Check following:

Dim oConnection         As ADODB.Connection
Dim oCommand            As ADODB.Command
Dim oRS                 As ADODB.Recordset
Dim sSQL                As String

Dim iVal
Dim oVal

    iVal = 1
    oVal = 1

  '-- Create a command object --
  Set oCommand = New ADODB.Command

  '-- Make an ODBC connection to the (local) SQL server,
  '-- connecting to the Pubs database with the default sa login and empty
password
  oCommand.ActiveConnection = "Provider=SQLOLEDB;Data
Source=Development;Initial Catalog=TimeConsultant;User
Id=sa;Password=;Connect Timeout=10"

  '-- define the name of the command
  oCommand.CommandText = "sp_JobsTest"

  '-- define the type of the command as a stored procedure (numeric value =
4)
  oCommand.CommandType = adCmdStoredProc

  oCommand.Parameters.Append oCommand.CreateParameter("RETURN_VALUE",
adInteger, adParamReturnValue, 4)

adParamInput, , iVal)

adInteger, adParamOutput, oVal)

  '-- execute the command
  Set oRS = oCommand.Execute

  MsgBox oCommand.Parameters(0).Value

When I run vb code, return code is blank. If I remove the select from the
stored procedure, I get the return value. What settings I'm is missing.

Thanks...

To get the return value, you must close the recordset first, for example:

Set oRS = oCommand.Execute
oRS.Close
MsgBox oCommand.Parameters(0).Value

Luke

(This posting is provided "AS IS", with no warranties, and confers no
rights.)