Skip to Content

Register Login

Default Header

(header konnte nicht geladen werden)

Navigation

Help to Display Recordset in DataGridView Control.

Created by: Ugo_Arrigoni at: 2/15/2019 5:04 PM (15 Replies)

Rating (2)

Thanks 6

Result pages:: |; 1 | 2; |

16 Entries

Entries per page: 10 | all

2/15/2019 5:04 PM	Rate (0)
Ugo_Arrigoni Posts: 32 Rating: (3)	Hello everybody. I'm actually wasting a lot of time in trying to achieve a solution for my problem. I have a workstation with WinCC Explorer and i need to comunicate with a database in other computer connected in my network. The database is in PostgreSQL and the communication i was able to achieve was via ODBC. I was able to retrieve values from my Postgresql table and write in tags but what i need is to somehow link the recordset query to the datasource of the DataGidView Control. My code: '### DIM VARS Dim objConnection Dim objCommand Dim objRecordset Dim strConnectionString Dim strSQL Dim lngCount '### CONFIG SQL strConnectionString ="Provider=MSDASQL;DSN=postgreSQL30_32;UID=postgres;PWD=view@123" strSQL = "SELECT * FROM production_log" objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open() objRecordset = CreateObject("ADODB.Recordset") objCommand = CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection objCommand.CommandText = strSQL objRecordset = objCommand.Execute lngCount = objRecordset.Fields.Count DataGridView1.DataSource = objRecordset '<<< THIS IS WHERE I STUCK So as you can see, i just need to show the query on the DataGridView and understand how to reference to it in the VBScript Editor. Please, any help is welcome and i really struggling to make it work. Thanks everyone for the attention.
Ugo_Arrigoni Posts: 32 Rating: (3)
Suggestion To thank Quote Answer

2/19/2019 7:25 PM	Rate (0)
Ugo_Arrigoni Posts: 32 Rating: (3)	Nobody gave a shat ?
Ugo_Arrigoni Posts: 32 Rating: (3)
Suggestion To thank Quote Answer

2/20/2019 9:09 AM	Rate (0)
PHS Silver Member Joined: 9/19/2009 Last visit: 5/10/2025 Posts: 460 Rating: (85)	Ugo_Arrigoni Nobody gave a shat ? Hi Ugo Take a look to and you will find a solution :-) https://support.industry.siemens.com/tf/ww/en/posts/using-a-datagrid-to-display/11348?page=1&pageSize=10 https://support.industry.siemens.com/tf/ww/en/posts/data-grid-control/15489?page=1&pageSize=10 https://support.industry.siemens.com/tf/ww/en/posts/owc-spreadsheet-for-show-sql-data-table/81691/?page=0&pageSize=10 Regards
	If my answer help you, use the Thanks or Rate Button, It is free of charge :-) The Powerful of WinCC I share my knowledge here: SCADA WinCC My contribution to the forum Library for WinCC
Suggestion To thank Quote Answer

2/25/2019 6:26 PM	Rate (0)
Ugo_Arrigoni Posts: 32 Rating: (3)	Thank you for the answer but i already have saw all those posts before. I dont know if you have read them but if you do, you will realize nobody found and answer. Said that, i cant understand how these are supposed to help. Is just a question of binding control "datasource" to my recordset, but it seems something is missing. I tried that guys custom activex control, but i feel that its just unreliable to apply it in a industrial scale.
Ugo_Arrigoni Posts: 32 Rating: (3)
Suggestion To thank Quote Answer

2/25/2019 8:48 PM	Rate (0)
PHS Silver Member Joined: 9/19/2009 Last visit: 5/10/2025 Posts: 460 Rating: (85)	Ugo_Arrigoni i cant understand how these are supposed to help. Hi Ugo. Sorry if this topics don´t help you. I was telling you, if you read this topics you will find a solution... may be it is not what you are looking for, but there are solutions... You can see the screenshots and you will see the data of database. Attachment sql.png (2368 Downloads)
	If my answer help you, use the Thanks or Rate Button, It is free of charge :-) The Powerful of WinCC I share my knowledge here: SCADA WinCC My contribution to the forum Library for WinCC
Suggestion To thank Quote Answer

3/18/2019 6:17 PM	Rate (0)
Ugo_Arrigoni Posts: 32 Rating: (3)	Well...I managed to achieve what i was needing. Actually DataGridView doesn't work directly with recordsets. For that i needed to use the control MSHFlexgrid. Then i just set via VBS the control Datasource to the RecordSet and it worked like a charm. Hope that this helps anybody in the future who faces this same problem. For using the recordset directly use the MSHFlexgrid and thats it. After stablishing connection, i just added this lines of code in the end and Voilà ! Set DBGrid.DataSource = objRecordset DBGrid.Refresh Set objRecordset = Nothing Set objCommand = Nothing Set objConnection = Nothing
Ugo_Arrigoni Posts: 32 Rating: (3)
Suggestion To thank Quote Answer
This contribution was helpful to 5 thankful Users Jen_Moderator F.Abril LluisPujol godfrey KITT

9/26/2019 5:42 PM	Rate (0)
Alping_Tech83 Member Joined: 3/29/2019 Last visit: 6/12/2024 Posts: 1 Rating: (0)	Hi Ugo, it's not clear to me how you managed to solve the problem with "MSHFlexGrid". From the code generated by me, a table is created with the correct number of rows and columns but the individual cells are not populated with SQL values. I attach the script: Dim DSN_Name, Com, Record, SQLtable, DatabaseName, ErrorDesc, TableName, SearchedName, ObjectGrid DatabaseName = SmartTags("DefinedDatabaseName") TableName = SmartTags("TableName") SearchedName = SmartTags("SearchedName") DSN_Name = "xxx" On Error Resume Next 'Create objects Set Com = CreateObject("ADODB.Connection") Set Record = CreateObject("ADODB.Recordset") 'Open data source and connection Com.Open = "Provider=MSDASQL; Initial Catalog=" & DatabaseName & "; DSN=" & DSN_Name & ";" 'DSN is name of the ODBC api 'SQL SQLtable = "SELECT * FROM " & TableName 'Execute SQL [SQLtable] Set Record = Com.Execute(SQLtable) 'Insert values from record field to "MSHFlexGrid" Set ObjectGrid = ScreenItems("Control1") 'Set ObjectGrid.Recordset = Record Set ObjectGrid.DataSource = Record ObjectGrid.Refresh ObjectGrid.ColWidth(-1) = 1280 'After execution close data source Com.Close 'Clear created objects Set Com = Nothing Set Record = Nothing Attachment Capture.PNG (1896 Downloads)
	Last edited by: Alping_Tech83 at: 09/26/2019 17:45:41
Suggestion To thank Quote Answer

11/22/2019 6:50 PM	Rate (0)
AdrianAVC Posts: 1 Rating: (0)	Did you manage to sort your problem? Can you please post a step by step on how you made it work. I'm struggling on my side and I didn't manage to make it work. Thanks
AdrianAVC Posts: 1 Rating: (0)
Suggestion To thank Quote Answer

11/28/2019 11:14 PM	Rate (0)
limbong Regular Member Joined: 2/18/2019 Last visit: 10/15/2025 Posts: 4 Rating: (0)	Ugo... would you mind if I ask you to see your code using MSHFlexGrid? Or maybe if you could tell me how to do it? Im stucked with the datagrid.... thank you Ugo_Arrigoni Well...I managed to achieve what i was needing. Actually DataGridView doesn't work directly with recordsets. For that i needed to use the control MSHFlexgrid. Then i just set via VBS the control Datasource to the RecordSet and it worked like a charm. Hope that this helps anybody in the future who faces this same problem. For using the recordset directly use the MSHFlexgrid and thats it. After stablishing connection, i just added this lines of code in the end and Voilà ! Set DBGrid.DataSource = objRecordset DBGrid.Refresh Set objRecordset = Nothing Set objCommand = Nothing Set objConnection = Nothing

Suggestion To thank Quote Answer

12/10/2020 8:54 PM	Rate (2)
Ugo_Arrigoni Posts: 32 Rating: (3)	Sub procedure1 '### DIMENSIONAMENTO DE VARIAVEIS Dim objConnection Dim objCommand Dim objRecordset Dim strConnectionString Dim strSQL Dim lngCount Dim DBGrid Set DBGrid = ScreenItems("control1") '### CONFIGURAÇÃO DA CONEXÃO SQL strConnectionString ="Provider=MSDASQL;DSN=postgreSQL30_32;UID=postgres;PWD=view@123" strSQL = "SELECT * FROM production_log ORDER BY timestamp DESC" '### INÍCIO DA CONEXÃO SQL Set objConnection = CreateObject("ADODB.Connection") 'Cria Conexão ADO objConnection.ConnectionString = strConnectionString objConnection.Open() Set objRecordset = CreateObject("ADODB.Recordset") 'Cria RecordSet Set objCommand = CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection objCommand.CommandText = strSQL Set objRecordset = objCommand.Execute 'Executa a Query lngCount = objRecordset.Fields.Count Set DBGrid.DataSource = objRecordset DBGrid.Refresh End Sub Sorry if it was a late response. I used MSHFlexgrid to display the recordset. Below there is a script to paint the rows with 2 colors and clean the table. Sub formata_grid() 'Dim DBGrid Dim j 'Set DBGrid = ScreenItems("control1") With DBGrid .AllowUserResizing = 0 'Impede o usuário de modificar os tamanhos das colunas e linhas. 'LARGURA DAS COLUNAS .ColWidth(0) = 1200 .ColWidth(1) = 1100 .ColWidth(2) = 1100 .ColWidth(3) = 1100 .ColWidth(4) = 1100 .ColWidth(5) = 1600 .ColWidth(6) = 1600 .ColWidth(7) = 1300 'ALTURA DAS LINHAS .RowHeight(-1) = 280 'Altura padrão para todas as linhas .RowHeight(0) = 400 'Altura do Header. 'ALINHAMENTO DOS HEADERS {0 = LeftTop; 1 = LeftCenter; 2 = LeftBottom; 3 = CenterTop; 4 = CenterCenter; etc...} .ColAlignmentHeader(-1) = 4 '(-1) significa que essa alteração e dará em todas as colunas ao invés de individualmente. 'ALINHAMENTO DAS COLUNAS .ColAlignment(-1) = 4 'CONFIGURAÇÕES DE FONTE '* HEADER * .Row = 0 For j = 0 To DBGrid.Cols - 1 .Col = j .CellBackColor = &HDEC4B0 '.CellFontBold = True '.TextStyleFIxed = 0 'Flat Next End With End Sub '### FUNÇÃO PARA DEIXAR AS CORES DAS LINHAS ZEBRADAS ( 2 CORES) ### Sub zebra() Dim i, j For i = 1 To DBGrid.Rows - 1 'i =1 pois se for = 0, vai colorir o Header também. DBGrid.Row = i If (DBGrid.Row Mod 2 = 0) Then 'Se o resto da divisão do número (i) por 2 for igual a 0, então o número é par, senão o número é ímpar. For j = 0 To DBGrid.Cols - 1 ' DBGrid.Col = j DBGrid.CellBackColor = &HE0E0E0 Next Else 'Continuando... Se o número for impar então... For j = 0 To DBGrid.Cols - 1 DBGrid.Col = j DBGrid.CellBackColor = &HA9A9A9 '&H808080 Next End If Next End Sub
Ugo_Arrigoni Posts: 32 Rating: (3)
Suggestion To thank Quote Answer
This contribution was helpful to 1 thankful Users Clorets

|

Share this page:

Share this page on...

Related topics by keywords:

WinCC Scripting

Follow us on