none
Filling Combobox with 2 columns from sql table, using diaplymember, valuemember RRS feed

  • Question

  • I am trying to populate a combobox in a windows form in powershell with the values from a sql table which is one column(Combobox.displaymember), and when selected in the combobox the output will be the data from the next column(combobox.valuemember). I am not sure how to connect, retrieve and bind the results. Currently i import the data to the column from an xml using the code below. Any help would be greatly appreciated.

        $XML.Options.Offices.office | %{$txtOffice.Items.Add($_)}
    Thursday, November 6, 2014 4:02 PM

Answers

  • I pulled this out of my junk box as it is an example of what is needed to use data.

    I already posted how to use a datatable with a form.

    $instance="$env:COMPUTERNAME\SQLExpress"
    $database='issue'
    $userid='dbuser1'
    $password='guessitnow'
    $connString="User ID=$userid;Password=$password;Initial Catalog=$database;Data Source=$instance"
    $SqlConnection=New-Object System.Data.SqlClient.SqlConnection $connString
    $SqlConnection.Open()
    
    $sqlCommand = $SqlConnection.CreateCommand()
    $sqlCommand.CommandText="SELECT COUNT(*) FROM GPLINKS_OUT WHERE TRANS_TYPE='ACG'"
    
    $adapter= New-Object System.Data.SqlClient.SqlDataAdapter $sqlCommand
    $dataset= New-Object System.Data.DataSet
    
    $rec=$adapter.Fill($dataset)
    Write-Host "Records returned=$rec"  -Fore green
    $SqlConnection.Close()
    
    foreach($a in $dataset.tables[0]){
        $a.demo_task_id
    }
    
    
    


    ¯\_(ツ)_/¯

    • Marked as answer by glacket Monday, November 10, 2014 2:27 PM
    Friday, November 7, 2014 7:07 PM

All replies

  • I think i can use the following properties to return the data but how would the connection string be and what else is missing?


    $JobCodelist.DisplayMember = "Supervisor Assistant"

    $JobCodeList.ValueMember = "34232"
    • Edited by glacket Thursday, November 6, 2014 4:22 PM
    Thursday, November 6, 2014 4:16 PM
  • We're not sure how to answer your question either, because we don't have your SQL table, and we also don't have any of your code other than the single line you posted. You need to put forth an effort to ask a better question. Keep in mind that we don't have access to your network and we can't see your screen. It is not possible to debug code we don't have that retrieves data from a database we don't have using an XML file we don't have.

    For the best help, please create a short, self-contained, correct example (see http://sscce.org/). The idea is to give respondents the absolute minimum amount of code needed to reproduce the problem so it fails exactly the same way for us as it does for you. I recommend leaving SQL out of it completely and just write a minimum example in PowerShell that retrieves data from a minimum data file that demonstrates your problem.

    Keep in mind that the forum respondents are volunteers who help for free. Help others to help you by providing enough information to help. It isn't polite to force others to guess and extract the needed pieces of information about your question little by little.


    -- Bill Stewart [Bill_Stewart]

    Thursday, November 6, 2014 4:22 PM
    Moderator
  • $combobox.DataSource=<datatable object>
    $combobox.DisplayMember=<sql columnname>
    $combobox.ValueMember=<sql columnname>

    Example:

        $combobox1.DataSource=$datatable
        $combobox1.DisplayMember='Customer Name'
        $combobox1.ValueMember='CustomerID'
    


    ¯\_(ツ)_/¯

    Thursday, November 6, 2014 6:09 PM
  • Here is a complete databinding example:

    	[void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
    	[void][reflection.assembly]::Load("System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
    
    [System.Windows.Forms.Application]::EnableVisualStyles()
    	$form1 = New-Object 'System.Windows.Forms.Form'
    	$textbox2 = New-Object 'System.Windows.Forms.TextBox'
    	$textbox1 = New-Object 'System.Windows.Forms.TextBox'
    	$customerList = New-Object 'System.Windows.Forms.ComboBox'
    	$buttonOK = New-Object 'System.Windows.Forms.Button'
    	$InitialFormWindowState = New-Object 'System.Windows.Forms.FormWindowState'	
    	
    	
    	$FormEvent_Load={
    		$datatable=New-Object System.Data.DataTable
    	    $col=$datatable.Columns.Add('CustomerID',[int])
    	    $col=$datatable.Columns.Add('Customer Name',[string])
    	    $datatable.Rows.Add(1,'Mary Baker')
    	    $datatable.Rows.Add(2,'John Baker')
    	    $datatable.Rows.Add(3,'Ted Baker')
    	    $datatable.Rows.Add(4,'Alice Baker')
    	    $customerList.DataSource=$datatable
    	    $customerList.DisplayMember='Customer Name'
    	    $customerList.ValueMember='CustomerID'
    	    $customerList.SelectedIndex=-1
    	}
    	
    	
    	$customerList_SelectedIndexChanged={
    	    $textbox2.Text=$customerList.Text
    	    $textbox1.Text=$customerList.SelectedValue
    	}
    	
    	$Form_StateCorrection_Load=
    	{
    		#Correct the initial state of the form to prevent the .Net maximized form issue
    		$form1.WindowState = $InitialFormWindowState
    	}
    	
    	$Form_Cleanup_FormClosed=
    	{
    		#Remove all event handlers from the controls
    		try
    		{
    			$customerList.remove_SelectedIndexChanged($customerList_SelectedIndexChanged)
    			$form1.remove_Load($FormEvent_Load)
    			$form1.remove_Load($Form_StateCorrection_Load)
    			$form1.remove_FormClosed($Form_Cleanup_FormClosed)
    		}
    		catch [Exception]
    		{ }
    	}
    
    # form1
    	#
    	$form1.Controls.Add($textbox2)
    	$form1.Controls.Add($textbox1)
    	$form1.Controls.Add($customerList)
    	$form1.Controls.Add($buttonOK)
    	$form1.AcceptButton = $buttonOK
    	$form1.ClientSize = '488, 215'
    	$form1.FormBorderStyle = 'FixedDialog'
    	$form1.MaximizeBox = $False
    	$form1.MinimizeBox = $False
    	$form1.Name = "form1"
    	$form1.StartPosition = 'CenterScreen'
    	$form1.Text = "Form"
    	$form1.add_Load($FormEvent_Load)
    	#
    	# textbox2
    	#
    	$textbox2.Location = '314, 39'
    	$textbox2.Name = "textbox2"
    	$textbox2.Size = '162, 20'
    	$textbox2.TabIndex = 3
    	#
    	# textbox1
    	#
    	$textbox1.Location = '247, 39'
    	$textbox1.Name = "textbox1"
    	$textbox1.Size = '61, 20'
    	$textbox1.TabIndex = 2
    	#
    	# customerList
    	#
    	$customerList.DropDownStyle = 'Simple'
    	$customerList.FormattingEnabled = $True
    	$customerList.Location = '21, 39'
    	$customerList.Name = "customerList"
    	$customerList.Size = '207, 163'
    	$customerList.Sorted = $True
    	$customerList.TabIndex = 1
    	$customerList.add_SelectedIndexChanged($customerList_SelectedIndexChanged)
    	#
    	# buttonOK
    	#
    	$buttonOK.Anchor = 'Bottom, Right'
    	$buttonOK.DialogResult = 'OK'
    	$buttonOK.Location = '401, 180'
    	$buttonOK.Name = "buttonOK"
    	$buttonOK.Size = '75, 23'
    	$buttonOK.TabIndex = 0
    	$buttonOK.Text = "OK"
    	$buttonOK.UseVisualStyleBackColor = $True
    
    	#Save the initial state of the form
    	$InitialFormWindowState = $form1.WindowState
    	#Init the OnLoad event to correct the initial state of the form
    	$form1.add_Load($Form_StateCorrection_Load)
    	#Clean up the control events
    	$form1.add_FormClosed($Form_Cleanup_FormClosed)
    	#Show the Form
    	$form1.ShowDialog()
    
    


    ¯\_(ツ)_/¯

    Thursday, November 6, 2014 6:24 PM
  • If you are using XML then it is not a dataset and cannot be bound.  You will have to convert it into an object collection and bind that.


    ¯\_(ツ)_/¯

    Thursday, November 6, 2014 7:03 PM
  • Hi Bill,

    I apologize, i was not trying to be rude and i appreciate you taking the time to read my post and try to help.

    Let me take a step back, I am a powershell noob and i Do not know what i am doing. I created a very basic form with a combobox and my goal is to bind a columns from a sql database that has 2 columns into my combobox. One column would be the displaymember property which would populate the combobox and the its corresponding value would be the output variable which i believe is combox.valuemember. jrv, i think i understand the example you posted and i have seen other similar examples, but the part i am having a problem with is connecting to a sql instance and binding the data to the combobox using datasource, valuemember and displaymember. I included both my entire form code and what i have so far for binding data to the combobox from a sql table. I am not how to tie the connection string to the $cbox variable.

    $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
    $cBox2.ValueMember = "code"
    $cBox2.DisplayMember = "Description"
    $cBox2.DataSource = dbo.tblpersonal

    Import-Module Activedirectory
    
    
    function button ($WF) {
    
    ###################Load Assembly for creating form & button######
    
    [void][System.Reflection.Assembly]::LoadWithPartialName( “System.Windows.Forms”)
    [void][System.Reflection.Assembly]::LoadWithPartialName( “Microsoft.VisualBasic”)
    
    
    #####Define the form size & placement
    
    $form = New-Object “System.Windows.Forms.Form”;
    $form.Width = 500;
    $form.Height = 190;
    $form.Text = $title;
    $form.StartPosition = [System.Windows.Forms.FormStartPosition]::CenterScreen;
    $form.ControlBox = $True
    
    
    ##############Define text label2
    
    $textLabel2 = New-Object “System.Windows.Forms.Label”;
    $textLabel2.Left = 25;
    $textLabel2.Top = 80;
    
    $textLabel2.Text = $WF;
    
    
    ############Define text box2 for input
    
    $cBox2 = New-Object “System.Windows.Forms.combobox”;
    $cBox2.Left = 150;
    $cBox2.Top = 80;
    $cBox2.width = 200;
    
    
    ###############"Add descriptions to combo box"##############
    $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
    $cBox2.ValueMember = "code"
    $cBox2.DisplayMember = "Description"
    $cBox2.DataSource = dbo.tblpersonal
    
    ############Define text box3 for input
    
    #############Define default values for the input boxes
    $defaultValue = “”
    $cBox2.Text = $defaultValue;
    #############define OK button
    $button = New-Object “System.Windows.Forms.Button”;
    $button.Left = 360;
    $button.Top = 45;
    $button.Width = 100;
    $button.Text = “Ok”;
    $Button.Cursor = [System.Windows.Forms.Cursors]::Hand
    $Button.Font = New-Object System.Drawing.Font("Times New Roman",12,[System.Drawing.FontStyle]::BOLD)
    ############# This is when you have to close the form after getting values
    $eventHandler = [System.EventHandler]{
    $cBox2.Text;
    $NameHash
    $form.Close();};
    
    $button.Add_Click($eventHandler) ;
    
    #############Add controls to all the above objects defined
    $form.Controls.Add($button);
    $form.Controls.Add($textLabel2);
    $form.Controls.Add($cBox2);
    $ret = $form.ShowDialog();
    
    #################return values
    
    return $cBox2.Text, $NameHash
    
    }
    
    $return= button “EmployeeID Utility”
    
    
    
    
        
    
    

    Thursday, November 6, 2014 9:59 PM
  • I recommend starting from scratch and learning how PowerShell works as well as ho to use ASP.Net.  After you learn those two things you can try learning how to use them with forms.

    What you are trying to do cannot be done.

    Here is a link with a good amount of beginner stuff that will get you started.

    http://technet.microsoft.com/en-us/scriptcenter/dd793612.aspx

    http://technet.microsoft.com/en-us/scriptcenter/dd742232


    ¯\_(ツ)_/¯

    Friday, November 7, 2014 12:33 AM
  • It seems to me like you are trying to run before you've learned to walk.

    I agree with jrv, the questions are very confusing and you seem to be missing some basics.


    -- Bill Stewart [Bill_Stewart]

    Friday, November 7, 2014 3:22 PM
    Moderator
  • I pulled this out of my junk box as it is an example of what is needed to use data.

    I already posted how to use a datatable with a form.

    $instance="$env:COMPUTERNAME\SQLExpress"
    $database='issue'
    $userid='dbuser1'
    $password='guessitnow'
    $connString="User ID=$userid;Password=$password;Initial Catalog=$database;Data Source=$instance"
    $SqlConnection=New-Object System.Data.SqlClient.SqlConnection $connString
    $SqlConnection.Open()
    
    $sqlCommand = $SqlConnection.CreateCommand()
    $sqlCommand.CommandText="SELECT COUNT(*) FROM GPLINKS_OUT WHERE TRANS_TYPE='ACG'"
    
    $adapter= New-Object System.Data.SqlClient.SqlDataAdapter $sqlCommand
    $dataset= New-Object System.Data.DataSet
    
    $rec=$adapter.Fill($dataset)
    Write-Host "Records returned=$rec"  -Fore green
    $SqlConnection.Close()
    
    foreach($a in $dataset.tables[0]){
        $a.demo_task_id
    }
    
    
    


    ¯\_(ツ)_/¯

    • Marked as answer by glacket Monday, November 10, 2014 2:27 PM
    Friday, November 7, 2014 7:07 PM
  • I understand. I am learning as i go. Again thanks for all the help. Since it appears that this cannot be done, easily and i do not have a complete understanding of this, I'll start a new question about pulling data from a csv instead. I actually understand that part of it.
    Monday, November 10, 2014 2:27 PM