Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB5,VB6
Expertise: Intermediate
May 29, 2002

A dual use of a lookup query

Usually in your application you use two different types of lookup query; the first one is for retrieving a page of records (or all records) to populate a pick list, the other one is to retrieve a single record, i.e. for decoding a code description while user is typing it in a textbox. So, if your queries are dynamically configured and are stored in a DB, you must duplicate the list of query statements. So you'll use in the first in the first situation a query like:

SELECT * FROM PUBLISHERS
In the other situation you'll use a query like:

SELECT * FROM PUBLISHERS WHERE COUNTRY = ?
But you can use just one query structured in dual mode:

SELECT * FROM PUBLISHERS WHERE (COUNTRY = ? OR 1 = ?)
When you want to populate a pick list you'll pass to the second parameter simply a 1 and all records will be retrieved. When you want to decode a single value, you must pass the code to the first parameter and a 0 to the second one. So, if all your queries use always one parameter to decode value, you can write a standard (dual) lookup routine like this:

Dim lConn As ADODB.Connection
Dim lCmd As ADODB.Command
Dim lRs As ADODB.Recordset
Dim lParAllRecords as Long
Dim lParSingleRecord as Long

Set lConn = New ADODB.Connection
lConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial " _
    & "Catalog=PUBS;Data Source=(local)"
lConn.CursorLocation = adUseClient
Set lCmd = New ADODB.Command
Set lCmd.ActiveConnection = lConn
lCmd.CommandText = txtStatement.Text

'if you want all records:
lParAllRecords = 1
lParSingleRecord = 0 'not important in the first use

'if you want a single record:
lParAllRecords = 0
lParSingleRecord = "Germany" 'the correct value to retrieve one record

lCmd.Parameters(0).Value = lParSingleRecord
lCmd.Parameters(1).Value = lParAllRecords
Set lRs = lCmd.Execute
Vito Vessia
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap