12 Replies Latest reply on Sep 9, 2013 10:02 PM by ainwood

    create an ODBCDataSet via VBA in processbook

    ainwood

      The sample code in the VBA help file is:

       

       

       
      Dim Application As Object
      Dim MyDatasets As Datasets
      Dim MyDataset As Dataset
      
      Set MyDatasets = Application.Datasets
      Set MyDataset = MyDatasets.Add("NewODBCDataSet",NULL, True, 1, True, pbcDatasetODBC)
      
      MyDataset.Query = "Select * from tlbData"
      

       Unfortunately, it doesn't work. 

       

      I've been trying to fix it, and have removed the "Dim Application as Object" (which is not needed in the VBA editor anyway). 

       

      I get an "Object required" error in the line: 

       
      Set MyDataset = Application.Datasets.Add("NewODBCDataSet", Null, True, 1, True, 3)
      

       Has someone got some sample code that actually works?

        • Re: create an ODBCDataSet via VBA in processbook
          hanyong

          Hi Andrew,

           

          How about getting the Datasets property from the active display (ThisDisplay) instead of getting it from the Application object? If you refer to ProcessBook Programmer Reference, it does mention something about The reference to DataSets at the Application level doesn't enumerate calculation and ODBC datasets.

           

          I have written something that adds a calculation dataset via VBA before here: http://vcampus.osisoft.com/discussion_hall/add_in_microsoft_development/f/11/p/2585/14059.aspx#14059

           

          Hopefully this can be helpful for you even though it is not adding an ODBC dataset.

            • Re: create an ODBCDataSet via VBA in processbook
              Marcos Vainer Loeff

              Hello Andrew,

               

               Please check the code snippet below that I have developed for you using the manual and Han Yong suggestions.

               

                

               
              Sub CreateODBCDataSet()
              
              Dim MyTrend As Trend
              Dim MyDataset As Dataset
              Dim MyDatasets As Datasets
              Dim MyOdbcDataset As ODBCDataset
              Dim MySymbols As Symbols
              
              
              Set MyDatasets = ThisDisplay.Datasets
              MyDatasets.Add "NewODBCDataSet", MyOdbcDataset, True, 1, True, pbDatasetODBC
              Set MyOdbcDataset = MyDatasets.GetDataset("NewODBCDataSet")
              With MyOdbcDataset
              
              .DataSourceName = "MARC-SQL"
              .Description = "Test Dataset"
              .Query = "SELECT TOP 10 FirstName FROM AdventureWorks.Person.Contact"
              
              End With
              
              MyDatasets.SetDataset MyOdbcDataset
              
              MsgBox "ODBC DataSet created!"
              End Sub
              

               

               

              Let me know if you still have any question,

                • Re: create an ODBCDataSet via VBA in processbook
                  ainwood

                  Thanks for the responses.  Apologies that I didn't respond sooner, but have simply not had time to get back to this!

                   

                  Anyway - I now have some code working that essentially adds a constant value as a dataset.  Code is as follows:

                   

                   

                   
                  '//Now create new datasets
                  Set highDataSet = AllDatasets.Add(m_sPITag & "_HIGH", Nothing, True, 1, True, pbDatasetPIExpression)
                  Set highDataSet = AllDatasets.GetDataset(m_sPITag & "_HIGH")
                  
                  With highDataSet
                      .Expression = m_dHighBound
                      .ColumnName = "Value"
                      .Interval = "10m"
                      .Description = "High SIC"
                  End With
                  AllDatasets.SetDataset highDataSet
                  

                   That works, and the dataset APPEARS to be created correctly.  Under "Tools | Datasets" from the PI Menu, it all looks good:

                   

                  2084.DataSet.JPG

                   

                  However, when I try to add it programatically to the trend, it doesn't display (no data, Invalid).  When I go to build mode and look at the dataset, it doesn't look right:

                   

                  As you can see, the value column isn't populated.  I can click-through the different text boxes in order to get it to work, but I really don't want to have to do that with every dataset. 

                   

                  4454.valueColumn.JPG

                   

                  Is there something I am doing wrong here?

                   

                   

                   

                   

                    • Re: create an ODBCDataSet via VBA in processbook

                      You have to add "DatasetName.Column" to the trend, i.e. WTE-28048_HIGH.Value as the trace to the trend.

                        • Re: create an ODBCDataSet via VBA in processbook
                          ainwood

                          Sorry - I should have been more clear:  I am doing that.

                           

                           

                           
                          Dim sHigh As String, sLow As String
                          sHigh = highDataSet.Name & "." & highDataSet.ColumnName
                          sLow = lowDataSet.Name & "." & lowDataSet.ColumnName
                          
                          
                          '//Now add to trend
                          oTrend.AddTrace (m_sPITag)                  '//PI Tag
                          oTrend.AddTrace (sHigh)
                          oTrend.AddTrace (sLow)
                          

                          It gets added to the trend, but appears on the trend as

                           

                          No Data

                           

                          Invalid

                           

                          ..and then examining the programatically created dataset shows the screen shots as per above.  In order to actually get it to display, I have to Edit the data set, then click in each of the fields (change no data at all, just click!) and then close.  The trend gets updated and it works.

                           

                          I'm just wondering what I am doing wrong in creating the dataset programatically.  Do I need to set another property?

                            • Re: create an ODBCDataSet via VBA in processbook
                              ainwood

                              Success!

                               

                              OK - believe it or not, the problem appears to be that in the expression statement, I was passing it a double, not a string.  

                               

                               

                               
                              highDataSet.Expression = m_dHighBound 
                              

                               I changed m_dHighBound (double data type) to m_sHighBound (string data type) and it worked.  Go figure....

                                • Re: create an ODBCDataSet via VBA in processbook
                                  ainwood

                                  Sorry - false alarm.  It is NOT working.  I had to manually edit the dataset to make it work, and didn't realise that I had done that between trials.

                                    • Re: create an ODBCDataSet via VBA in processbook
                                      Marcos Vainer Loeff

                                      Hello Andrew,

                                       

                                       

                                       

                                      Please refer to the code snippet below and inform us if it has worked properly.

                                       

                                       

                                       
                                      sTraceName = MyDataset.Name & "." & MyDataset.Columns.Item(2)
                                      

                                       

                                        • Re: create an ODBCDataSet via VBA in processbook
                                          ainwood

                                          No, I get an error: "Number submitted was outside acceptable range for the value".  

                                           

                                          I checked, and the Column Count of the Dataset is zero.  I then tried to use the .Columns.Add ("Value") method, and still the column count was set to zero.

                                           

                                          Not sure what to do. Here is the full code - dump it into a PI Display, and make sure there is a blank trend (Trend1) on the display.  Run it and confirm that:

                                           

                                          1.) A dataset is created and added to the trend.

                                           

                                          2.) The dataset displays no data ("No Data Invalid")

                                           

                                          3.) The dataset is available in the "Tools | DataSets" menu.  If you "Edit" it, then click in the textbox for the PI Expression Field, then in the textbox for the "Description" field, and then save it, it suddenly starts working on the trend display.

                                           

                                           

                                           
                                          Option Explicit
                                          Option Compare Text
                                          Sub TestDataSet()
                                          '//Sub to test adding a dataset with a constant value to the display, then adding it to a trend.
                                          Dim i As Long
                                          
                                          '//Now remove any datasets that already have the names...
                                          Dim AllDatasets As Datasets
                                          Dim ConstantDataSet As PIExpressionDataset
                                          
                                          '//Set a reference to the DataSets Collection
                                          Set AllDatasets = ThisDisplay.Datasets
                                          
                                          '//Set name of dataset
                                          Const sConstantName As String = "ConstantSet"
                                          
                                          '//Remove all existing traces (so that rerunning this does not add a duplicate trace)
                                          For i = ThisDisplay.Trend1.TraceCount To 1 Step -1
                                              ThisDisplay.Trend1.RemoveTrace (i)
                                          Next i
                                          
                                          '//Check if there is already a dataset by this name, and remove it.
                                          Dim ThisDataset As Dataset
                                          For Each ThisDataset In AllDatasets
                                              If ThisDataset.Name = sConstantName Then
                                                  AllDatasets.Remove (sConstantName)
                                              End If
                                          Next ThisDataset
                                          
                                          '//Add a new dataset
                                          Set ConstantDataSet = AllDatasets.Add(sConstantName, Nothing, True, 1, True, pbDatasetPIExpression)
                                          
                                          '//Now have to open it (apparently...)
                                          Set ConstantDataSet = AllDatasets.GetDataset("ConstantSet")
                                          
                                          '//Now edit it.
                                          With ConstantDataSet
                                              .Expression = 10                '//Dataset is to have a constant value of "10"
                                              '.Columns.Add ("Value")
                                              .ColumnName = "Value"
                                              .Interval = "10m"
                                              .Description = "Constant Value"
                                          End With
                                          
                                          '//Now save changes for the dataset
                                          AllDatasets.SetDataset ConstantDataSet
                                          
                                          Dim sTraceName As String
                                          sTraceName = ConstantDataSet.Name & "." & ConstantDataSet.ColumnName
                                          
                                          '//Now add to a trend
                                          ThisDisplay.Trend1.AddTrace (sTraceName)
                                          
                                          End Sub
                                          

                                           I did notice that the dataset names are Upper Case, but trying to adjust for that change that made no difference.