Desktop productivity for business analysts and programmers

Trying to bind SAS Library to DataGridView in Custom Task

Reply
Frequent Contributor
Posts: 81

Trying to bind SAS Library to DataGridView in Custom Task

I'm trying to bind a SAS Library to a DataGridView object in an EG Custom Task (VB.NET Express 2010 + EG 5.1).  I'm currently using the following code:

    Public Overrides Function Show(ByVal Owner As  _

        System.Windows.Forms.IWin32Window) As  _

        SAS.Shared.AddIns.ShowResult

        Dim server As String = String.Format("{0}", Consumer.ActiveData.Server)

        Dim libref As String = String.Format("{0}", Consumer.ActiveData.Library)

        Dim member As String = String.Format("{0}", Consumer.ActiveData.Member)

        Dim dlg As WhereViewerForm = New WhereViewerForm(settings)

        ' set Active Data

        dlg.TextServer.Text = server

        dlg.TextLibref.Text = libref

        dlg.TextMember.Text = member

        dlg.DataGridView.DataSource = New SasLibrary(server, libref)

        dlg.DataGridView.DataMember = member

        ' show form

        If (DialogResult.OK = dlg.ShowDialog(Owner)) Then

            ' save the settings from the form

            settings = dlg.Settings

            Return ShowResult.RunNow

        Else : Return ShowResult.Canceled

        End If

    End Function

I get the following error message:

Exception Details:

----------------------------------------

Exception type:  System.ArgumentException

Message:        Child list for field CARS cannot be created.

Source:          System.Windows.Forms

Target Site:    EnsureListManager

Stack Trace:

  at System.Windows.Forms.BindingContext.EnsureListManager(Object dataSource, String dataMember)

  at System.Windows.Forms.DataGridView.DataGridViewDataConnection.SetDataConnection(Object dataSource, String dataMember)

  at System.Windows.Forms.DataGridView.set_DataMember(String value)

  at WhereViewer.WhereViewer.Show(IWin32Window Owner)

  at SAS.Tasks.Toolkit.SasTask.ShowDialog(IWin32Window Owner, ViewType PreferredView)

  at SAS.Shared.AddIns.Management.AddInProxy.ShowDialog(IWin32Window Owner, ViewType PreferredView)

  at SAS.EG.ProjectElements.EGTask.Show(IWin32Window Owner)

  at SAS.EG.MainForm.AddTask(Guid taskGuid, Element inputData, Boolean bIsWizard, String xml, String templateName, String templateNameFullPath)

  at SAS.EG.MainForm.LaunchTask(Element target, Guid taskGuid, Boolean bIsWizard, String xml, String templateName, String templateNameFullPath)

  at SAS.EG.MainForm.OnTaskClick(Object sender, EventArgs e)

  at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)

  at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)

  at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)

  at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)

  at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)

  at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)

  at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)

  at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)

  at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

  at System.Windows.Forms.Control.WndProc(Message& m)

  at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

  at System.Windows.Forms.ToolStrip.WndProc(Message& m)

  at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)

  at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

  at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

  at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I know I'm using the wrong method for allocating the library.member to the DataGridView, but what should I use instead? .....................Phil

Community Manager
Posts: 2,692

Re: Trying to bind SAS Library to DataGridView in Custom Task

Phil,

You need to bind this to something that implements a IEnumerable, such as a Collection or a List.  The SasLibrary object itself does not fit that, but SasLibrary.GetSasDataMembers() does - it returns a list of SasData objects.

Here's a C# example (sorry, it's what I have handy) to populate a listbox with a collection of SAS data set members from a selected library.

lbMembers.Items.Clear();

SasLibrary lib = lbLibraries.SelectedItem as SasLibrary;

if (!lib.IsAssigned)

{

    try

    {

        lib.Assign();

    }

    catch { }

}

if (lib.IsAssigned)

{

    lbMembers.Items.AddRange(new List<SasData>(lib.GetSasDataMembers()).ToArray());

}

Chris

Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

Chris,

I used your code to help me populate the selection lists, but have decided to use BindingSource to connect the SAS table to DataGridView, so I can add a filter. However, I'm still a bit confused as to how I allocate the SAS table to BindingSource using Consumer + server name + libref name + member name, not helped by Microsoft using "DataSet" to mean something similar to SAS library.

All I'm trying to create is an EG Add-in that functions a bit like the old SAS System Viewer.

........Phil

PS. Sorry for insisting on VB code, but I've never got the hang of C/C++/C#.  ;-)

Community Manager
Posts: 2,692

Re: Trying to bind SAS Library to DataGridView in Custom Task

Hi Phil,

Even though this caused me extreme pain and made my eyes bleed, I came up with a VB example.  It even features an optional filter, which is where I suspect you were going next.

dgview.PNG

It's a whopping 15 lines of code to connect the DataGridView to a SAS data set and fill the grid with values:

Dim ds As New DataSet

Dim where As String

where = ""

If Not String.IsNullOrEmpty(txtFilter.Text) Then

  where = String.Format("WHERE {0}", txtFilter.Text)

End If

Using con = New SasServer(Consumer.AssignedServer).GetOleDbConnection()

  Dim command As New OleDbCommand(String.Format("select * from {0} {1}", txtData.Text, where), con)

  Dim adapter As New OleDbDataAdapter(command)

  adapter.Fill(ds)

  If ds.Tables.Count > 0 Then

  dgView.DataSource = ds.Tables(0)

  Else : MessageBox.Show("No table found!")

  End If

End Using

I've attached the complete project as a ZIP file to this message.  Here's hoping that it comes through.

Cheers,

Chris

Attachment
Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

Chris,

Many thanks, particularly for the obvious pain you put yourself through to complete it in VB!

One final question, in your descriptions of the Project Reviewer and SAS Macro Variable Viewer Custom Tasks you mentioned making these task modeless, rather than modal, so you can keep them open while performing other actions in EG.  This task appears to be modal, but what would I need to do to make it modeless instead?

Many thanks in advance...........Phil

Community Manager
Posts: 2,692

Re: Trying to bind SAS Library to DataGridView in Custom Task

Phil,

That part is easy: change the line in the SasDataGrid.vb from:

form.ShowDialog(Owner)

to:

form.Show(Owner)

You have to be a bit careful with modeless tasks, as the state of the project, server connections, etc. can change while your task is displayed.  And if you want the task to run in Highlander mode (you know, "there can be only one!"), then you need to add code to check that another instance of the task isn't already showing, in case the end user tries to invoke the task from the menu again.

Chris

Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

Chris,

That is perfect!

Not sure, but I think I may need to allow multiple copies to exist, provided they are accessing different SAS data sets, as the users may like the opportunity to use the task to compare data sets.  However, I'm going to test that situation thoroughly first before I release it "into the wild".

Finally, and not requiring any sort of code at the moment, are there any other ways that SAS data sets could be bound to the DataGridView?  The reason I ask is that I've not found SQL processing to be as quick as accessing data through the IDataService, so maybe there have been some other ways to access data developed since 2009 when I wrote about this in my self-published SAS book.

In the meanwhile, thank you for providing me with a working prototype for the DataGrid custom task, and I'll keep you posted on any new developments.

Thanks again.........Phil

Community Manager
Posts: 2,692

Re: Trying to bind SAS Library to DataGridView in Custom Task

I'm glad that the example works for you.

This particular SQL is not like PROC SQL - this SQL is a command that is issued to the SAS IOM Data Provider, which then uses the DataService to open and retrieve the data.  I tend to use this method because it is flexible.  I can easily filter rows, control sequence of fields, as well as select only certain fields if that's what I want.

I don't have an example in .NET, but you could use the RecordSet object to open a particular table directly.  See my PowerShell example of this.  You'll have to map the PowerShell commands to VB.NET, but you don't want me to spoon-feed you too much, do you?

Chris

Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

Chris,

I've had a look at your PowerShell example, and, as I like a challenge, I'll try this out as an alternative technique.  The last custom task I worked on was for EG 3.0/4.1, and the environment for custom tasks in EG 4.2+ is not quite the same, so I'm suffering a certain amount of pain getting up to speed again.

Many thanks for your support and new ideas……Phil

Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

Chris,

As promised I'm reporting my progress.  The custom task has progressed a lot to look like the following screenshot, with most input fields automatically scaling: :smileyplus:

Unfortunately all the values from the SAS data sets are displayed unformatted, which fine for most columns, but not so good for dates & times.

Is it possible to switch the SAS formatting on, if required, rather than just having it switched off? :smileyconfused:

Thanks.............Phil

Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

Chris,

The only way I've found to circumvent this problem, so far, is to add a copy of the formatted value in a character variable:

ScreenShot009.png

Is this a feature of the OleDbConnection, and would SqlClient.SqlConnection supply anything different to the DataGridView?

............Phil

Community Manager
Posts: 2,692

Re: Trying to bind SAS Library to DataGridView in Custom Task

Phil, I think that you need to communicate the "SAS Formats=_ALL_" property into your connection or recordset object, depending on your approach. 

I don't have a specific example (other than the PowerShell version I provided), but check the OLE DB Cookbook for details.

Chris

Frequent Contributor
Posts: 81

Re: Trying to bind SAS Library to DataGridView in Custom Task

I was given another useful link by SAS Technical Support, which nearly solves the problem, but it was written for Visual Basic 2005, not 2010:

http://support.sas.com/kb/26/145.html#ADODB.VB.snippet

Getting closer.............Phil

Ask a Question
Discussion stats
  • 12 replies
  • 1204 views
  • 7 likes
  • 2 in conversation