TN AppSvr189 How to populate data from a SQL Server Table into an ArchestrA graphic Combo Box

This Article walks you through how extract data from a SQL Server Table and populate this into an ArchestrA graphic Combo Box.

In this example we will take an employee name out of the employee table and populate this information into the Combo Box.

This article from InSource shows...

• Author: Alex Davenport
• Published: 10/03/2016
• Applies to: Application Server

1. Login to SQL Management Studio and choose the table you wish to capture the data from as seen below.
   
   Database = Employe
   Table = EmployeeTable
   Column = Employee
   
2. Create a new ArchestrA graphic within the Application Server and place the Combo Box on this.
   
3. Right Click on the canvas and select scripts as we will need to create an On Show script as seen below. Keep the Data Timeout at the default of 1000 ms then click the OK button.
   
   The script is as follows and you will need to input the details relevant to your environment

dim connectstr as string;
dim SQLConn as System.Data.SqlClient.SqlConnection;
dim SQLCmd as System.Data.SqlClient.SqlCommand;
dim SQLRdr as System.Data.SqlClient.SqlDataReader;
dim sQuery as string;
ConnectStr = "Server=" + "machine name"+ ";Database=" + "SQL Database name" + ";UID=" + "sql username" + ";PWD=" + "password" + ";";
SQLConn = new System.Data.SqlClient.SqlConnection(ConnectStr);
SQLConn.Open();
sQuery = "SELECT [Employee]";
sQuery = sQuery + "FROM [EmployeeTable]";
SQLCmd = New System.Data.SqlClient.SqlCommand(sQuery, SQLConn);
SQLRdr = SQLCmd.ExecuteReader();
While SQLRdr.Read()
EmployeeName = SQLRdr("Employee");
ComboBox1.AddItem(EmployeeName);
endwhile;
SQLRdr.Close();
SQLConn.Close();

4. Right Click on the canvas and select Custom Property. Create a Custom Property String called EmployeeName then click OK button. This populates the the Combo Box with the names obtained from the database column.
   
5. Save and close the ArchestrA graphic and open InTouch WindowMaker. Create a new Window and place the ArchestrA graphic on the canvas as seen below.
   
6. Switch to Runtime and when you click on the down arrow within the Combo Box the data extracted from the SQL Database is now available in the Combo Box.