topic How to connect 3 comboboxes and Textbox? POWERSHELL and WPF in Vault Customization Forum

How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Fri, 04 Dec 2015 11:45:18 GMT

Hi everybody,

here is my problem. I have 3 comboboxes that read data from mdf file. Selected value from the first one will affect on what shows in the second one, and selected value from the second one will affect on what shows in the third one. Result from the third one will populate in the textbox.
See attached picture.

Here is a code from PowerShell.






function GetData()
{
	$objOleDbConnection1= New-Object "System.Data.OleDb.OleDbConnection"
	$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
	$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
	$objDataTable = New-Object "System.Data.DataTable"
	$objOleDbConnection1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Script\test_1.mdb;"
	$objOleDbConnection1.Open()
	$objOleDbCommand.Connection = $objOleDbConnection1
    $objOleDbCommand.CommandText = "SELECT SORT + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur like 'A__' ORDER BY ID ASC"
	#set the Adapter object
	$objOleDbAdapter.SelectCommand = $objOleDbCommand
	#fill the objDataTable object with the results
	$objOleDbAdapter.Fill($objDataTable)
	return $objDataTable.DefaultView
	$objOleDbConnection1.Close()
	
}

And here is code from Combobox no 1 i WPF

<ComboBox x:Name="mdb" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275" Grid.Column="1"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur" ItemsSource="{Binding PsList[GetData]}" SelectedIndex="0"/>

QUESTION IS: How to connect all these 3 comboboxes and Textbox? Of cource i need to create 2 more comboboxes 🙂

Greetings
Lukas

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Sat, 05 Dec 2015 11:43:47 GMT

Anyone?:(

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Mon, 07 Dec 2015 14:38:11 GMT

Can anyone help me out here? I'm really stacked, and can't find any way to break it through...I managed to trigger seperate comboboxes and return a value to combobox in Data Standard Dialog window. I was sure that by this i can send value of

$Prop["Tegningstype"].Value back to Powershell, and put it into the new query that is in the functions triggered by combobox no.2.

Unfortunately it does not work, value from $prop is not returned to Powershell.

Is it wrong way to do this or i forgot about something here?

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Wed, 09 Dec 2015 16:20:11 GMT

Come on people:)

So many experts er here... No one knows how to do it???

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

wangdu — Fri, 11 Dec 2015 13:22:06 GMT

Hi,

you could do the following.

Hook an event handler to first combobox selectionchanged event.

$firstcombobox = $dsWindow.FindName("mdb")
$firstcombobox.add_SelectionChanged(
{
param($sender,$args)
})

Inside the handler you can collect the selected value which you can use to get the values for the second combobox

$selectedValue = $sender.SelectedValue
$itemsForSecondCombobox = #collect the items
$secondcombobox = $dsWindow.FindName("SecondCombobox")
$secondcombobox.ItemsSource = $itemsForSecondCombobox

Repeat the same for the second combobox and third combobox
For the third combobox use the selectedvalue to populate the textbox

Hope it helps!

Wangdu

coolOrange

www.coolOrange.com

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Fri, 11 Dec 2015 17:39:25 GMT

Thanks for responce. I'm a newbe in this kind of programming...and might ask stupid questions, but what you mean by "hooking to"?

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Fri, 11 Dec 2015 18:20:14 GMT

could you please walk me through???

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

wangdu — Mon, 14 Dec 2015 07:52:35 GMT

Hi,

In the InitializeWindow function of the Default.ps1 file, Add the following:

$firstcombobox = $dsWindow.FindName("mdb")
$secondcombobox = $dsWindow.FindName("SecondCombobox")
$thirdcombobox= $dsWindow.FindName("thirdcombobox")

$firstcombobox.add_SelectionChanged(
{
param($sender,$args)
FeedCombobox($sender.SelectedValue, $secondCombobox)
})

$secondcombobox .add_SelectionChanged(
{
param($sender,$args)
FeedCombobox($sender.SelectedValue, $thirdcombobox)
})

$thirdcombobox.add_SelectionChanged(
{
param($sender,$args)
$textbox= $dsWindow.FindName("textbox1")
$textbox.Text = $send.SelectedValue
})

Now define the FeedCombobox function like this in the Default.ps1. This function can be called for both from first and second combobox selectionchanged event handler.

function FeedCombobox($selectedValue, $cmbbx)
{
     $itemsForCombobox = #collect the items using the selectedValue by making sql queries here
     $cmbbx.ItemsSource = $itemsForCombobox 
}

Please make sure that you have defined three comboboxes and textbox with the name used in the script. Note also that the SelectedValuePath is used and defined correctly.

Hope it helps!

Wangdu

coolOrange

www.coolOrange.com

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Mon, 14 Dec 2015 10:11:28 GMT

Thanks you very much for all your help.

Here is what i have now in WPF:

<ComboBox x:Name="mdb" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275" Grid.Column="1"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur" ItemsSource="{Binding PsList[GetData]}" SelectedIndex="0" />
            <ComboBox x:Name="SecondCombobox" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur" SelectedIndex="0" Margin="0,43,0,0" Grid.Column="1" />
            <ComboBox x:Name="thirdcombobox" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275" Grid.Column="1"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur"  SelectedIndex="0" Margin="0,91,0,0"/>

And here is content of the ps.1

function InitializeWindow
{
	$dsWindow.Width = 600
	$dsWindow.Height = 400
	$dsDiag.ShowLog()
	$dsDiag.Clear() 
	$firstcombobox = $dsWindow.FindName("mdb")
	$secondcombobox = $dsWindow.FindName("SecondCombobox")
	$thirdcombobox= $dsWindow.FindName("thirdcombobox")

	$firstcombobox.add_SelectionChanged(
	{
	param($sender,$args)
	FeedCombobox($sender.SelectedValue, $secondCombobox)
	})

	$secondcombobox.add_SelectionChanged(
	{	
	param($sender,$args)
	FeedCombobox($sender.SelectedValue, $thirdcombobox)
	})

	$thirdcombobox.add_SelectionChanged(
{	
	param($sender,$args)
	$textbox= $dsWindow.FindName("text_combobox")
	$textbox.Text = $send.SelectedValue
	})

function FeedCombobox($selectedValue, $cmbbx)
{
    $objOleDbConnection1= New-Object "System.Data.OleDb.OleDbConnection"
	$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
	$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
	$objDataTable = New-Object "System.Data.DataTable"
	$objOleDbConnection1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Script\test_1.mdb;"
	$objOleDbConnection1.Open()
	$objOleDbCommand.Connection = $objOleDbConnection1
    $objOleDbCommand.CommandText = "SELECT SAPstruktur + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur like 'NK' ORDER BY ID ASC" 
	$itemsForCombobox = "SELECT SAPstruktur + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur = '$selectedValue' ORDER BY ID ASC" 
    $cmbbx.ItemsSource = $itemsForCombobox 
	#set the Adapter object
	$objOleDbAdapter.SelectCommand = $objOleDbCommand
	#fill the objDataTable object with the results
	$objOleDbAdapter.Fill($objDataTable)
	#$mdb.ItemsSource = @($objDataTable)
	#$mdb.DisplayMemberPath = "Expr1000"
	#$mdb.SelectedValuePath = "SAPstruktur"
	#To display the "raw" contents, just enter
	return $objDataTable.DefaultView
	$objOleDbConnection1.Close()
	
}

Content of combobox "mdb"

function GetData
{
	$objOleDbConnection1= New-Object "System.Data.OleDb.OleDbConnection"
	$Tier2ClassificationCategory = New-Object System.Windows.Forms.ComboBox
	$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
	$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
	$objDataTable = New-Object "System.Data.DataTable"
	$objOleDbConnection1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Script\test_1.mdb;"
	$objOleDbConnection1.Open()
	$objOleDbCommand.Connection = $objOleDbConnection1
    $objOleDbCommand.CommandText = "SELECT SORT + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur like 'A__' ORDER BY ID ASC"
	#set the Adapter object
	$objOleDbAdapter.SelectCommand = $objOleDbCommand
	$Tier2ClassificationCategory.SelectedIndex = -1
	#fill the objDataTable object with the results
	$objOleDbAdapter.Fill($objDataTable)
	#To display the "raw" contents, just enter
	return $objDataTable.DefaultView
	$objOleDbConnection1.Close()
}

Before each of comboboxes had its own "GetData" function. Now logically only the first one will have it.

I'm not sure if i do all correct, because i do not get any results in Secondcombobox.

Do i need to do any changes to binding between comboboxes and ps1? What should be binded to ItemsSource i WPF?

Thanks a lot!

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

wangdu — Mon, 21 Dec 2015 11:29:37 GMT

Hi,

For filling up the second combobox, you have to do it similar to how you have done in the GetData function. The GetData function returns the $objDataTable.GetDefaultView(). So, then, instead of now returning it in FeedCombobox function, it assigns it directly to the $cmbbx.ItemsSource. Here's an example below:

#trimmed 
$objOleDbCommand.CommandText = "SELECT SORT + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur like 'A__' ORDER BY ID ASC"
#set the Adapter object
$objOleDbAdapter.SelectCommand = $objOleDbCommand
$objOleDbAdapter.Fill($objDataTable)
$cmbbx.ItemsSource = $objDataTable.DefaultView

Hope it helps!

Wangdu

coolOrange

www.coolOrange.com

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Fri, 22 Jan 2016 08:10:08 GMT

Thanks for responce...Could you please show me "literally" how to do this? f.e. linking cmb1 and cmb2???
It does not work for me her .(

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Fri, 22 Jan 2016 08:31:40 GMT

hmm i have looked at this problem once again. And i if get $cmbbx.ItemsSource. from the first cmbx how to use this in order to read from database new values. As i tried to explan, the thing is to get in the first cmbx full lists of values (this works). Then with picked up value form the first cmbx use this value to selec values for the second cmbx. And similar action for the third one. So to picture this a bit better imagine that you er i a library and there is a choise: 1. Type of books (drama, crime aso). - reader picked the type 2. Then he get a choice of "language" - reader picked "english" 3. Then the third choise of "writer" - reader picked "tom Clancy". This is how i want to organize my comboboxes. I make 3 selects from the database, one for each cmbx... Step 1 is done :)) Step 2 and 3...does not work.....:)

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

wangdu — Fri, 22 Jan 2016 14:38:54 GMT

Hi again,

Lets say this is your xaml

<ComboBox x:Name="mdb" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275" Grid.Column="1"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur" ItemsSource="{Binding PsList[GetData]}" SelectedIndex="0" />
            <ComboBox x:Name="SecondCombobox" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur" SelectedIndex="0" Margin="0,43,0,0" Grid.Column="1" />
            <ComboBox x:Name="thirdcombobox" HorizontalAlignment="Left" VerticalAlignment="Top" Width="275" Grid.Column="1"  DisplayMemberPath="Expr1000" SelectedValuePath="SAPstruktur"  SelectedIndex="0" Margin="0,91,0,0"/>

And the content of the ps1

function InitializeWindow
{
	$dsWindow.Width = 600
	$dsWindow.Height = 400
	$dsDiag.ShowLog()
	$dsDiag.Clear() 
	$firstcombobox = $dsWindow.FindName("mdb")
	$secondcombobox = $dsWindow.FindName("SecondCombobox")
	$thirdcombobox= $dsWindow.FindName("thirdcombobox")

	$firstcombobox.add_SelectionChanged(
	{
	param($sender,$args)
        $commandText = "SELECT SAPstruktur + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur = '$sender.SelectedValue' ORDER BY ID ASC"
	FeedCombobox($secondCombobox,, $commandText)
	})

	$secondcombobox.add_SelectionChanged(
	{	
	param($sender,$args)
         $commandText = "SELECT SAPstruktur + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur = '$sender.SelectedValue' ORDER BY ID ASC"
	FeedCombobox($thirdcombobox, $commandText)
	})

	$thirdcombobox.add_SelectionChanged(
	{	
	param($sender,$args)
	$textbox= $dsWindow.FindName("text_combobox")
	$textbox.Text = $send.SelectedValue
	})
}

function FeedCombobox($cmbbx, $commandText)
{
    $objOleDbConnection1= New-Object "System.Data.OleDb.OleDbConnection"
	$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
	$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
	$objDataTable = New-Object "System.Data.DataTable"
	$objOleDbConnection1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Script\test_1.mdb;"
	$objOleDbConnection1.Open()
	$objOleDbCommand.Connection = $objOleDbConnection1
	#set the Adapter object
	$objOleDbAdapter.SelectCommand = $objOleDbCommand
	#fill the objDataTable object with the results
	$$objOleDbCommand.CommandText = $commandText
        #set the Adapter object
        $objOleDbAdapter.SelectCommand = $objOleDbCommand
        $objOleDbAdapter.Fill($objDataTable)
        $cmbbx.ItemsSource = $objDataTable.DefaultView
        $objOleDbConnection1.Close()	
}

function GetData
{
	$objOleDbConnection1= New-Object "System.Data.OleDb.OleDbConnection"
	$Tier2ClassificationCategory = New-Object System.Windows.Forms.ComboBox
	$objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
	$objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
	$objDataTable = New-Object "System.Data.DataTable"
	$objOleDbConnection1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Script\test_1.mdb;"
	$objOleDbConnection1.Open()
	$objOleDbCommand.Connection = $objOleDbConnection1
    $objOleDbCommand.CommandText = "SELECT SORT + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur like 'A__' ORDER BY ID ASC"
	#set the Adapter object
	$objOleDbAdapter.SelectCommand = $objOleDbCommand
	$Tier2ClassificationCategory.SelectedIndex = -1
	#fill the objDataTable object with the results
	$objOleDbAdapter.Fill($objDataTable)
	#To display the "raw" contents, just enter
	return $objDataTable.DefaultView
	$objOleDbConnection1.Close()
}

Make sure that the sql query for the thirdcombobox should be replaced with correct query. And also to make sure that a TextBox control in the xaml with the name text_combobox is defined.

Hope now it works!

Wangdu

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Sat, 23 Jan 2016 17:35:30 GMT

Mane thanks for your help! I so appreciate it! 🙂

I have done copy/paste the code shown above. It seems to be problem with finding second combobox. If i define fixed query (without dynamic value, - SAPstruktur = "fixed value") there is nothing shown i second combobox (and it should show one value selected from the database). What might be missing?? some dll or something i the code?? I do not get it...how to check that combobox has be found in the window. I have checked all names and all seems to be ok.
Can i send you ps1 and xaml on e-mail so you can look at it??

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Sat, 23 Jan 2016 17:47:07 GMT

the same here...textbox er not filled up. I have added textbox line to check of there any value sent from the combobox.

Or, there something wrong with sent value (=0 ?)

Do i need to make any changed to Inventor.cfg?? ( register some variable or something?)

$firstcombobox.add_SelectionChanged(
	{
	param($sender,$args)
        $commandText = "SELECT SAPstruktur + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur = '$sender.SelectedValue' ORDER BY ID ASC"
	FeedCombobox($secondCombobox, $commandText)
	$textbox= $dsWindow.FindName("text_combobox")
	$textbox.Text = $send.SelectedValue
	})

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Sun, 24 Jan 2016 14:42:19 GMT

There is something wrong..i cannot even hide/show controls from Powershell script. Nothing works, $dsDiag.inspect() - no reaction...script cannot find eny controls in xaml..what might be wrong here??? Any clue?

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

wangdu — Wed, 27 Jan 2016 16:16:37 GMT

Hi,

I am seeing a typo in the code you posted ($send instead of $sender).

As for finding out the issues with the empty combobox, writing out the intermediate states to the DataStandard Log window could help you debug your code.

e.g.

$firstcombobox.add_SelectionChanged(
{
	param($sender,$args)
        $dsDiag.ShowLog()#shows the log window
        $commandText = "SELECT SAPstruktur + ' ' + '-' + ' ' + Tekst, SAPstruktur FROM SAPstruktur where SAPstruktur = '$sender.SelectedValue' ORDER BY ID ASC"
	FeedCombobox($secondCombobox, $commandText)
	$textbox= $dsWindow.FindName("text_combobox")	
        $selectedValue = $sender.SelectedValue
        $dsDiag.Trace(">> $($selectedValue)")#writes to the log window
})

Hope it helps!

Wangdu

Re: How to connect 3 comboboxes and Textbox? POWERSHELL and WPF

L00K — Mon, 01 Feb 2016 07:53:12 GMT

Hi, thanks for replay. I have made a new set of files: default.ps1 and inventor.xaml. And used only code for comboboxes. $dsWindow.Name works fine and selected value from firstcombobox is nicely send to textbox. Now, i tried to go further with code you send me and it looks like DataStandard does not like: FeedCombobox($secondcombobox, $commandText). If i "comment" this line then DataStadard window appears, but if i remove "#" then it crashes...I guess there is some little issue that Powershell Script does not like. Any idea what might be wrong with this line? Best Regard Lukas