Q-mation Informer

: Populating a Listbox in InTouch from a Relational Database

InTouch SQL Access Manager is a feature within InTouch that allows an InTouch to interact with relational databases. A database table may contain data that would be useful to present to an operator in an InTouch application – e.g., Recipes to select, Work Orders to run, Part Numbers, etc. InTouch Windows Controls - List boxes, Combo boxes, and Text boxes - are available in the WindowMaker wizard library and can be used to display list-type data. The following Q-Tip describes how to query a column in a database table, and populate an InTouch listbox (Click here to download a copy of this application).

This QTip assumes basic familiarity with InTouch SQL Access. A SQLConnect() statement should be used in the Application-On Startup script to first connect to the specific database. Refer to the Volume 3: May/June 2005 Q-mation Informer for a QTip on Using ADO Connection Strings in InTouch SQL Access – www.qmation.com.

In order to connect database information with InTouch information, a Bindlist must be created in InTouch. The Bindlist is located under the SQL Access Manager area on the Explorer tree in WindowMaker.

The Bindlist creates a direct relationship between the column name in the database table and the tagnames in InTouch. This way, the SQL Access script functions know where to locate the selected data for retrieval, insertion or update. In this example, the InTouch tags “UserNameTag” and “PhoneNumberTag” are associated with database column names UserName and PhoneNumber, respectively.

In order to populate a list box with information, the records in the database must first be queried out of a table. Next, a process must cycle through each record, inserting each one into a memory message tag associated with the list box.

The following scripting can be placed in the Window - On Show script of the window where the list box (i.e. ListBox_1) resides:

The wcClear() function works as an initialization to first clear the contents of ListBox_1 each time prior to populating it with current information. “UserList” and “ItemList”, the name of the database table, and the specific InTouch Bindlist are assigned to InTouch message tags TableName and Bindlist, respectively, below.

wcClear ( "ListBox_1" );

TableName = "UserList";

BindList = "ItemList";

The WhereExpr message tag sets the filter for the query to allow all (or a subset of) records to be returned. This may be modified to filter a particular Recordset – e.g., only return recipes for Reactor1, etc. Note its location in the subsequent SQLSelect() statement.

WhereExpr = "";

The SQLSelect() statement selects where and how to query the data that will populate the list box.

ResultCode = SQLSelect( ConnectionID, TableName, BindList, WhereExpr,"");

The SQLFirst() statement locates the first record in the database table and loads it into memory.

ResultCode = SQLFirst(ConnectionID);

The For-Next Loop Script will cycle through all the records, starting from the first one retrieved in the SQLFirst() statement above.

The SQLNumRows() returns the total number of rows in the table (used for looping through all rows returned.)

The wcAddItem() script will insert each record (now mapped to a message tag called UserNameTag) into the ListBox_1.

The SQLNext() statement indexes to the next record in the query result set:

Dim Loop as INTEGER;

FOR Loop = 1 TO SQLNumRows(ConnectionID)

wcAddItem ( "ListBox_1", UserNameTag); {Add each record's item into ListBox_1}

IF Loop < SQLNumRows(ConnectionID) THEN {If not at the last returned record, ...}

SQLNext( ConnectionID ); {... move to the next record}

ENDIF;

NEXT;

At this point, the listbox will be populated with data. You could put this script in an InTouch QuickFunction, and call it from the Window – On Show script, or possibly from a “Refresh” button an the window which would re-query and repopulate the listbox.

Please feel free to email support@qmation.com with questions.