Use Datagrid Control get SQL data.

Hi, to do this without Industrial Graphics will be a challenge since there is no built in SQL script library that can be used to link a dataset to the ActiveX Control's DataSource property. (requires data-type DataTable/DataSet)

Legacy InTouch uses SQL Access manager and bindlists to link rows of data from a dataset to InTouch tags. 

This would work much easier if you would have possibility to use Industrial Graphics, I know you state that it is not available, but could be worth exploring?

In legacy InTouch where a datagrid was needed, we have created our own custom ActiveX that also manages the connection and query of data to a database, and not only the display the data, but I do not know if I would recommend that today since it is not the best solution for a future-proof solution, I would expect the support for ActiveX to be removed in future versions since it is a legacy technology that becomes harder and harder to maintain supported.

Hi, to do this without Industrial Graphics will be a challenge since there is no built in SQL script library that can be used to link a dataset to the ActiveX Control's DataSource property. (requires data-type DataTable/DataSet)

Legacy InTouch uses SQL Access manager and bindlists to link rows of data from a dataset to InTouch tags. 

This would work much easier if you would have possibility to use Industrial Graphics, I know you state that it is not available, but could be worth exploring?

In legacy InTouch where a datagrid was needed, we have created our own custom ActiveX that also manages the connection and query of data to a database, and not only the display the data, but I do not know if I would recommend that today since it is not the best solution for a future-proof solution, I would expect the support for ActiveX to be removed in future versions since it is a legacy technology that becomes harder and harder to maintain supported.

Thank you, Richard. At first, I think I can link to the SQL and display the data through some default Active X property, such as #DataGrid1.datasource=XXXX or other simple methods like these, after hearing what you said about custom Active X, I have decided to give up this idea and change to use Industrial Graphics.

Thank you.

If you have Industrial Graphics available and want to use that microsoft control, you can import the .Net version of the grid, available from System.Windows.Forms.dll

This, combined with the AVEVA aaDBIntegration script library should have you all set.

It is of course possible to use .Net scripting for the SQL Connection part, but the Aveva library has some advantages and can be executed Asynchronous.

It is available by default in a Application Server solution but has to be imported in a stand alone InTouch application.

https://docs.aveva.com/bundle/sp-appserver/page/829001.html

To import the Client Controls in your InTouch Application

Also import aaDBIntegration.aaSLIB, default location is

C:\Program Files (x86)\ArchestrA\Framework\Bin

Then you are all set to embed the DataGridView in a symbol

Then you can connect to your datasource and retrieve the data, loop through the result set or bind the datataset to your grid.

This example is quite rudimentary, it is advised to use Stored Procedures and Asynchronous methods to retrieve the data to avoid lock up of your application. But it should get you started.

Please reefer to the aaDBIntegration documentation for further information

Dim Connection As aaDBClient.aaDBConnection;
Dim Command    As aaDBClient.aaDBCommand;
Dim resultCode As Integer;
Connection = aaDBAccess.GetConnection("", aaDBConnectionType.Sql);
command = connection.createcommand("Select MaterialID, MaterialName from Materials", aadbcommandtype.sqlstatement, true);
'or use SP, with parameters
'command = connection.createcommand("MySP", aadbcommandtype.StoredProcedure, true);
'Command.SetCharParameterByName("Param1","",aaDBParameterDirection.Input,"10");

resultCode = Command.ExecuteSync();
LogMessage("Execute select");

If resultCode <> 0 Then  
	LogMessage("***ERROR*** Failed to execute Command, got result code "+ResultCode);
Else
	If Command.ExecutionState == aaDBTransactionState.Completed Then
		dim Rows as integer;
		LogMessage("Query Completed");
		'Use table as datasource
		DataGridView1.DataSource = Command.GetDataset().Tables(0);
		
		'Or loop through result to use as needed
		Dim i As Integer;
		Command.SelectTable(0); 
		Rows = Command.RowCount;
		IF( Rows > 0) THEN
			FOR i=0 TO (Rows-1) Command.SelectRow(i);
				LogMessage("Data on row "+i+" :"+Command.GetCurrentRowColumnByName("MaterialID"));
			NEXT;			
		endif;
		LogMessage("rows added");
	EndIf;
EndIf;

Command.Dispose();
Connection.Dispose();

‌Thank you very much, I will have a try.

And I find a default SQLDataGrid tool to link and display the SQL data. It may not be as flexible in functionality, but it compensates with straightforward connections. 

Thank you.

‌

Indeed! Good thing that you mention this component as an alternative. 
Good luck!