p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB Databases Basics (http://p2p.wrox.com/forumdisplay.php?f=82)
-   -   Setting a Variable = Result of a SQL Query (http://p2p.wrox.com/showthread.php?t=761)

PeteS June 20th, 2003 01:17 AM

Setting a Variable = Result of a SQL Query
 
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


All times are GMT -4. The time now is 10:45 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.