Setting a Variable = Result of a SQL Query

PeteS · June 20th, 2003, 01:17 AM

I want to retreive one piece of text data from the database and assign it to a String variable. The SQL statement that would successfully retreive the correct piece of data would be something like:

"SELECT FirstName FROM Employee WHERE EmpID = x"

where x would be a value set in the VB application.
How would I set a variable, say "CurrentEmp", = to the result of that query?

TIA,
Pete S - Tucson, AZ

Jeff Mason · June 20th, 2003, 08:14 AM

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