Data Grid Control

I have spent long time trying to find ANYTHING about the Siemens S7 Grid Control that is available even in PCS7 v7. Attached piece of code demonstrates how to use that grid control and how to adjust column width.


Dim arrCOM_DB(6,33)
Dim arrTAG_DB(6,33)
Dim cnt
Dim cntr

Dim TagGrp
Dim objDBGrid
Dim x

Set objDBGrid = ScreenItems("DB_COMM_AJ")

'-- Prepare Tags

arrTAG_DB(1,1) = "CFC(1)/3.QCOMEN01"
arrTAG_DB(2,1) = "CFC(1)/3.QCOMTY01"
arrTAG_DB(3,1) = "CFC(1)/3.QCOMNA01"
arrTAG_DB(4,1) = "CFC(1)/3.QCOMDN01"
arrTAG_DB(5,1) = "CFC(1)/3.QBPAEN01"
'--

'-- etc etc .. all 32 sections

'--
arrTAG_DB(1,32) = "CFC(1)/3.QCOMEN32"
arrTAG_DB(2,32) = "CFC(1)/3.QCOMTY32"
arrTAG_DB(3,32) = "CFC(1)/3.QCOMNA32"
arrTAG_DB(4,32) = "CFC(1)/3.QCOMDN32"
arrTAG_DB(5,32) = "CFC(1)/3.QBPAEN32"

'-- Ad Tags to the Tag Group

Set TagGrp = HMIRuntime.Tags.CreateTagSet

For cnt = 1 To 32

For cntr = 1 To 5

TagGrp.Add arrTAG_DB(cntr,cnt)

Next

Next

'-- Read Tags from the Tag Group

TagGrp.Read

'-- Fill the Data Grid

objDBGrid.LockUpdate True

x = objDBGrid.InsertRows(1,32)

objDBGrid.CellValue(0,0)="-No-"
objDBGrid.CellValue(0,1)="COMM ENUM"
objDBGrid.CellValue(0,2)="TYPE"
objDBGrid.CellValue(0,3)="NAME"
objDBGrid.CellValue(0,4)="DENS"
objDBGrid.CellValue(0,5)="BPA ENUM"

For cnt = 1 To 32

For cntr = 1 To 5

arrCOM_DB(cntr,cnt) = TagGrp(arrTAG_DB(cntr,cnt)).Value
objDBGrid.CellValue(cnt,cntr)= arrCOM_DB(cntr,cnt)

Next

Next

objDBGrid.LockUpdate False

'-- Resize the Column (fix size)
objDBGrid.GetColumn(1).Width = 50

'-- Resize the Columns (optimize)
objDBGrid.GetColumn(2).ResizeColWidthsToFit
objDBGrid.GetColumn(3).ResizeColWidthsToFit
objDBGrid.GetColumn(4).ResizeColWidthsToFit
objDBGrid.GetColumn(5).ResizeColWidthsToFit

Set objDBGrid = Nothing
Set TagGrp = Nothing

One more piece of code, this time with data coming fromORACLE database. I didn't figure out how to do it with RecordSet directly, but my workaround with a loop to copy data over works fine:


Dim lngReccount
Dim lngCount

Dim objConnection
Dim objRecordset
Dim objCommand
Dim objReccount

Dim strDBName
Dim strSrvName
Dim strConnectionString
Dim strSQL
Dim strServer

Dim objDBGrid
Dim x

Dim objQCOMEN
Dim objQCOMTY
Dim objQCOMNA
Dim objQCOMDN
Dim objQBPAEN

Dim arrCOM_OES(8,3000)
Dim cnt
Dim cntr
Dim cntMax

Set objDBGrid = ScreenItems("OES_COMM")

cnt = 0
cntr = 0
cntMax = 0

'-- Intitialize the Array
For cnt=1 To 1000

arrCOM_OES(1,cnt) = 0'--PCELL_ID
arrCOM_OES(2,cnt) = "x"'--FCAT_NAME
arrCOM_OES(3,cnt) = "x"'--PARAM_NAME
arrCOM_OES(4,cnt) = "x"'--ENUM_ID
arrCOM_OES(5,cnt) = "x"'--SITE
arrCOM_OES(6,cnt) = "x"'--ENUM_VALUE


Next

objDBGrid.NumRows = 0
cnt = 0

'-- Read Values from ORACLE

'-- Setting up connection string - new, without using TNSNAMES.ORA

strConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=DEVSRV)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=BABOEBP))); uid=abc;pwd=xyz;"

'-- Creating and opening ADODB Connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open

'-- Creating and opening Recordset and SQL Command Objects
Set objRecordset = CreateObject("ADODB.Recordset")
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

'-- Reccount SQL String to count number of records
strSQL = "SELECT COUNT (*) FROM OES_CNF_FPARAM_ENUM"
objCommand.CommandText = strSQL

'-- Execute and fill the Recordset
Set objRecordset = objCommand.Execute
'-- Read the Record Count (selected as count(*))
lngReccount = objRecordset.Fields(0).Value

'-- New SQL String
strSQL = "SELECT * FROM OES_CNF_FPARAM_ENUM"
objCommand.CommandText = strSQL

'-- Execute and fill the Recordset
Set objRecordset = objCommand.Execute

'-- Get Count of Fields
lngCount = objRecordset.Fields.Count
'--


'-- If there are any fields then
'-- (Oracle does not like the condition lngReccount>0 here)

If ((lngCount>0)) Then

'--objReccount.OutputValue = lngReccount



objRecordset.movefirst



cnt = 0

While Not objRecordset.EOF

cnt = cnt + 1
cntMax = cnt

For cntr = 0 To 5
If Len(objRecordset.Fields(cntr).Value) > 0 Then
arrCOM_OES(cntr,cnt) = objRecordset.Fields(cntr).Value
End If
Next

objRecordset.movenext

Wend




End If

'-- Fill the Data Grid

objDBGrid.LockUpdate True

x = objDBGrid.InsertRows(1,cntMax)

objDBGrid.CellValue(0,0)="-No-"
objDBGrid.CellValue(0,1)="PCELL_ID"
objDBGrid.CellValue(0,2)="FCAT_NAME"
objDBGrid.CellValue(0,3)="PARAM_NAME"
objDBGrid.CellValue(0,4)="ENUM_ID"
objDBGrid.CellValue(0,5)="SITE"
objDBGrid.CellValue(0,6)="ENUM_VALUE"

'objDBGrid.LockUpdate False

'-- If there are any fields then
If ((lngCount>0)) Then '-- And (lngReccount>0)) Then

For cnt = 1 To cntMax


For cntr = 0 To 5

objDBGrid.CellValue(cnt,cntr+1)= arrCOM_OES(cntr,cnt)

Next

Next

End If

objDBGrid.LockUpdate False

Set objDBGrid = Nothing

'-- Delete all objects
Set objCommand = Nothing

'-- Close ADODC Connection
objConnection.Close

'-- Delete all objects

Set objConnection = Nothing
Set objRecordset = Nothing
Set objReccount = Nothing

New question published by rachnaarul is split to a separate thread with the subject How to display SQL database table in Wincc?.

Best regards
Jen_Moderator