View Single Post
  #2 (permalink)  
Old June 20th, 2003, 08:14 AM
Jeff Mason Jeff Mason is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

You don't indicate your database environment, so this may have to be adapted for your particular situation.

There are two ways.

1. Open a recordset from the query and read the row. Recordset.fields(0).value will have the value returned, as:

CurrentEmp = Recordset.fields(0).value

2. Use a command object and execute a stored procedure which returns a string parameter. A SQL Server stored procedure definition might be along the lines of:
Code:
Create Procedure GetName
  @EmpID integer
  @CurrentEmp varchar(whatever)
as
  set nocount on
  SELECT @CurrentEmp=FirstName FROM Employee WHERE EmpID = @EmpID;
The command object has two parameters: an input parameter giving the EmpID you want to select, and an output parameter which will contain the result. Something like:
Code:
   Dim cmd as Command
   Set cmd = New Command
   With cmd
      .CommandType = adCmdStoredProc
      .CommandText = "GetName"
      .Parameters.Append .CreateParameter(, adInteger, dParamInput, ,EmpID)
      .Parameters.Append .CreateParameter(, adVarChar, adParamOutput, <lengthofname>)
      Set .ActiveConnection = <your connection>
   End With
   cmd.Execute , , adExecuteNoRecords
   CurrentEmp=cmd.Parameters(1).value
The command object/stored procedure method is way more efficient since a recordset object is pretty "heavy".

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote