Using the ODBC Query Component

These steps describe how to return data from the Microsoft SQL Chinook database and display the data in a datagrid on a Windows form using an ODBCQuery component.

1. Create a Windows form with a button, text box, and data grid. Here is an example:
   
   

2. Add the TableView component to the Windows form. It is displayed in the Component tray.

3. Add an automation to the solution.

4. Add the ODBCQuery component to the automation. It is displayed on the Global tab of the Component tray as required.

5. Browse the ConnectionString property for the query. Select the connection type and specify the connection parameters for the datasource. Examples of the Data Link setup dialog tabs are shown here:
   
   

6. Click the Test Connection button in the Data Link Properties dialog to make sure you have a valid connection to the data source.

7. Once you receive validation of the connection, click OK to save the settings and return to Studio. The ConnectionString property will be populated based on the settings you chose in the Data Link Properties window. Here is an example: ConnectionString: Dsn=MS Sql Server;persist security info=False;uid=User.

8. Make sure Text is selected as the CommandType.

9. Browse the CommandText property to open the SQL Command Text window. Enter the following SQL statement: "select * from Customers where Country =?" Here is an example:
   
   

10. Click OK to close the SQL Command Text dialog once you have entered the SQL statement.

11. Verify that the ReturnType property is set to ReturnsRows.

12. Browse the Parameters property to open the OdbcParameter Collection Editor. Set the Size property to 99 and the value to USA.

13. Click OK to save the parameter information and close the editor.

14. Click the Test Query link in the Properties grid. Note that you must test the query for the query parameters to be fully defined in the project. If you run the project without testing the query in Studio, an error is thrown. Click the Test Query link to test the query with the parameters.

15. When prompted to execute the SQL command against the connected database, click Yes.

16. An information box indicates that the query executed successfully if you defined your ConnectionString, CommandText, and Parameters correctly. Click OK to close the information box.

17. Drag the Execute method for the ODBCQuery to the automation. Note that a parameter is exposed for input of the where clause parameter.

18. Connect the solution components as shown in the following automation:
    
    

19. Return to the Windows form in the Designer and select the TableView object.

20. Click on the TableProvider property to expose available data sources. The query should be listed here as follows:
    
    

21. Select the oDBCQuery1 component as the TableProvider. For more information, see TableView Component.

22. Select the DataGrid component from the toolbox. Click the DataSource property and select the TableView1 object as the data provider. The data you retrieved when you tested the query displays in the DataGrid.

23. Save and run your solution.

24. Enter a Country name in the text box and click Get Data. The ODBCQuery returns data for the country you selected.
    

 

---

Privacy | Trademarks | Terms of Use | Feedback

Updated: 18 June 2020

© 2016 - 2020 Pegasystems Inc.  Cambridge, MA All rights reserved.