7 Replies Latest reply on May 14, 2018 1:46 PM by Rick Davin

    PI SQL query result in DataGridView through VB.net

    igreguri

      I'm trying through VB.net to make some quick query viewer, and I have problems to show result of SQL queries in DataGridView.

      Can someone give me an example please (with PI connection string).

       

      Thanks,

      Igor

        • Re: PI SQL query result in DataGridView through VB.net
          Rick Davin

          Hi Igor,

           

          This question feels very broad.  It would help if you can post examples of what you've tried and what is giving you difficulty.  When I read "I have problems to show result of SQL queries in DataGridView", my first thought is that you have successfully generated results from a SQL query and the problem you are experiencing is with loading those results into a DataGridView.  But since you want an example with a PI connection string, it sounds like your problem may be in generating the results from a SQL query. 

           

          Are you also using a BindingSource?  I frequently use them along with a DGV.  I typically set the DGV.DataSource to be a BindingSource.

           

          Are you using WinForm or WPF?

            • Re: PI SQL query result in DataGridView through VB.net
              igreguri

              Hi Rick,

              I using WinForm.

               

              Code...

              Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
                    s = server.Servers("MyServer")
                      'On Error Resume Next
                      s.Open("piadmin, """)
                      If Err.Number <> 0 Then
                          RichTextBox1.AppendText(Now() & Chr(9) & Err.Description & Chr(13))
                          Exit Sub
                      End If
                      RichTextBox1.AppendText(Now() & Chr(9) & "Connect->Connected to server " & s.Name & Chr(13))
              
                      Dim conn As New SqlConnection("Provider=PIOLEDB;Data Source=MyServer;Integrated Security=SSPI;Persist Security Info=False;Connect Timeout=2880;Command Timeout = 600000")
                      Dim MySQLAdapter As New SqlDataAdapter
                      Dim MyTable As New DataTable
                      Try
                          conn.Open()
                          MySQLAdapter.SelectCommand = New SqlCommand("SELECT s.tag as PI_Point,s.value,d.engunits as Unit,d.descriptor as Description, format (time,'dd.MM.yyyy  HH:mm:ss') as Time FROM [piarchive].[pisnapshot] s INNER JOIN [pipoint].[classic] d ON s.tag = d.tag WHERE s.tag LIKE 'RT%.LA' and s.time > '*-7d'", conn)
                          MySQLAdapter.Fill(MyTable)
                          DataGridView1.DataSource = MyTable
                          s.Close()
                      Catch ex As SqlException
                          MessageBox.Show(ex.Message)
                      Finally
                          conn.Dispose()
                      End Try
                  End Sub
              

               

               

              Query...

                • Re: PI SQL query result in DataGridView through VB.net
                  Rick Davin

                  What happens when you run this?  Does the SqlException get thrown?  As is, you are only catching 1 very specific exception.  I would recommend either (A) including a general Catch block after line 21 to catch any other exception being thrown (to help you diagnose further), or else (B) change line 20 to be general Exception instead of SqlException.

                   

                  Have you tested the SQL query anywhere else and feel it is correct?

                    • Re: PI SQL query result in DataGridView through VB.net
                      igreguri

                      When I start, form opens and nothing more.

                      I tested SQL query in PI SQL Commander.

                      Do you have any similar example with DataGridView that works?

                        • Re: PI SQL query result in DataGridView through VB.net
                          Rick Davin

                          OK,  so the query is good.  Have you tried stepping through the code in Debug mode?  See if it reaches line 18, and then what happens next?  Does it jump to the Finally?

                           

                          Again, you may want to catch other exceptions to help figure this out.

                          1 of 1 people found this helpful
                            • Re: PI SQL query result in DataGridView through VB.net
                              igreguri

                              I changed the code a bit and now it works... Thanks!

                               

                              Imports System.Data.OleDb.OleDbConnection
                              
                                  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
                                      s = server.Servers("MyServer")
                                      'On Error Resume Next
                                      s.Open("piadmin, """)
                                      If Err.Number <> 0 Then
                                          RichTextBox1.AppendText(Now() & Chr(9) & Err.Description & Chr(13))
                                          Exit Sub
                                      End If
                                      RichTextBox1.AppendText(Now() & Chr(9) & "Connect->Connected to server " & s.Name & Chr(13))
                              
                                      'Dim conn As New SqlConnection("Provider=PIOLEDB;Data Source=MyServer;Integrated Security=SSPI;Persist Security Info=False;Connect Timeout=2880;Command Timeout = 600000")
                                      Dim conn As New System.Data.OleDb.OleDbConnection("Provider=PIOLEDB;Data Source=INARNRPI;Integrated Security=SSPI;Persist Security Info=False;Connect Timeout=2880;Command Timeout = 600000")
                                      Dim MySQLAdapter As New System.Data.OleDb.OleDbDataAdapter()
                                      Dim MyTable As New DataTable
                                      Try
                                          conn.Open()
                                          MySQLAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand("SELECT s.tag as PI_Point,s.value,d.engunits as Unit,d.descriptor as Description, format (time,'dd.MM.yyyy  HH:mm:ss') as Time FROM [piarchive].[pisnapshot] s INNER JOIN [pipoint].[classic] d ON s.tag = d.tag WHERE s.tag LIKE 'RT%.LA' and s.time > '*-7d'", conn)
                                          MySQLAdapter.Fill(MyTable)
                                          Dim bindingSource As New BindingSource
                                          bindingSource.DataSource = MyTable
                                          DataGridView1.AutoGenerateColumns = True
                                          DataGridView1.DataSource = bindingSource
                                          DataGridView1.Refresh()
                                          s.Close()
                                      Catch ex As SqlException
                                          MessageBox.Show(ex.Message)
                                      Finally
                                          conn.Dispose()
                                      End Try
                                  End Sub