dcsimg
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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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
Thanks for your registration, follow us on our social networks to keep up-to-date