Pass parameters from a input box to stored procedure using powershell-please read the whole thing, I know it's long

Question

Hi have a script to create a input box using powershell. It looks like this

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 

$objForm = New-Object System.Windows.Forms.Form 
$objForm.Text = "Data Entry Form"
$objForm.Size = New-Object System.Drawing.Size(300,200) 
$objForm.StartPosition = "CenterScreen"

$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter") 
{$x=$objTextBox.Text;$objForm.Close()}})
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape") 
{$objForm.Close()}})

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,120)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"
$OKButton.Add_Click({$x=$objTextBox.Text;$objForm.Close()})
$objForm.Controls.Add($OKButton)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,120)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = "Cancel"
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,20) 
$objLabel.Size = New-Object System.Drawing.Size(280,20) 
$objLabel.Text = "Please enter @from date:"
$objForm.Controls.Add($objLabel) 


$objTextBox = New-Object System.Windows.Forms.TextBox 
$objTextBox.Location = New-Object System.Drawing.Size(10,40) 
$objTextBox.Size = New-Object System.Drawing.Size(260,20) 
$objForm.Controls.Add($objTextBox) 

 $objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()

$from

Now I want to pass the value $from I input into stored procedure parameter @from, I tried below but not working. Any suggestion?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=AMSDataWarehouse      Test;Integrated Security=SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand


 $SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.CommandText = "YQBreport1"
$cmd.Parameters.Add("@from", $from)| Out-Null
$Command.ExecuteNonQuery()
$from = $Command.Parameters["@from"].value


$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$SQLResult =$DataSet.Tables[0]
$commands = $SQLResult | foreach-object -process { $_.output }> output.ps1
.\output.ps1

possible duplicate of How to provide param attributes for a script in Powershell? — Andrew Savinykh
– Andrew Savinykh, Commented Mar 8, 2013 at 9:01
If you google for 'powershell parameters' you'll get this technet.microsoft.com/en-us/magazine/jj554301.aspx and this devcentral.f5.com/blogs/us/powershell-abcs-p-is-for-parameters and other useful hits. — Andrew Savinykh
– Andrew Savinykh, Commented Mar 8, 2013 at 9:03

Robert · Accepted Answer · 2013-07-01 15:13:48Z

1

The line:

$OKButton.Add_Click({$x=$objTextBox.Text;$objForm.Close()})

Only works within the Add_click functionality. To make it accessible elsewhere you need to alter it slightly to:

$OKButton.Add_Click({$Global:x=$objTextBox.Text;$objForm.Close()})

$x is then a global parameter that you can pass to your procedure.

edited Jul 1, 2013 at 15:13

Robert

5,31843 gold badges70 silver badges115 bronze badges

answered Jul 1, 2013 at 14:56

user2539452

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

E.V.I.L. · Accepted Answer · 2013-03-08 09:03:39Z

0

Yeah you can make a GUI in PowerShell using Windows Presentation Foundation (WPF). I've tried and failed.

Read in values like this:

$from = Read-Host '@From'
$from

Out-GridView is something new I just learned. You should look into it.

ls | Out-GridView -PassThru -Title 'GUI?'

answered Mar 8, 2013 at 9:03

E.V.I.L.

2,16613 silver badges14 bronze badges

2 Comments

Poorkenny Over a year ago

You just made me realize Out-Gridview allows the selection of one or more entries, that are returned to the caller script... Thanks! :D Though I don't think that will help @Apriljuly, given that it only provides a list to select from, not an input box.

E.V.I.L. Over a year ago

Petri

Petr Havlicek · Accepted Answer · 2013-03-08 12:58:30Z

0

Nice example how to create an input box in PowerShell (using Windows Forms)

http://technet.microsoft.com/en-us/library/ff730941.aspx

answered Mar 8, 2013 at 12:58

Petr Havlicek

2,1312 gold badges19 silver badges28 bronze badges

3 Comments

Apriljuly Over a year ago

That's a very good input box, thank you, but how do I associate what I input in it with the parameter in my stored procedure, do you know?

Petr Havlicek Over a year ago

This should do the trick: $SqlCmd.Parameters.Add("@from", $x) | Out-Null. Assuming that you will use the example mentioned which assigns text entered in the dialog to $x variable.

Petr Havlicek Over a year ago

What do you mean by not working? Does it throw any exception or error message?

Collectives™ on Stack Overflow

Pass parameters from a input box to stored procedure using powershell-please read the whole thing, I know it's long

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related