devxlogo

A dual use of a lookup query

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 mustduplicate 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.ConnectionDim lCmd As ADODB.CommandDim lRs As ADODB.RecordsetDim lParAllRecords as LongDim lParSingleRecord as LongSet lConn = New ADODB.ConnectionlConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial " _    & "Catalog=PUBS;Data Source=(local)"lConn.CursorLocation = adUseClientSet lCmd = New ADODB.CommandSet lCmd.ActiveConnection = lConnlCmd.CommandText = txtStatement.Text'if you want all records:lParAllRecords = 1lParSingleRecord = 0 'not important in the first use'if you want a single record:lParAllRecords = 0lParSingleRecord = "Germany" 'the correct value to retrieve one recordlCmd.Parameters(0).Value = lParSingleRecordlCmd.Parameters(1).Value = lParAllRecordsSet lRs = lCmd.Execute

See also  lenso.ai - an AI reverse image search platform
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist