Trying to pass multiple parameters to a sql stored procedure.

[rhdyes]

I have written and tested a stored procedure in my ms sql database I am now trying to pass multiple parameters and run it. This one is just a simple insert procedure. I debug the powershell script and it opens the connection with the server but fails to pass the parameters. with the below error.

Exception calling "ExecuteNonQuery" with "0" argument(s): "Procedure or function 'sp_insertmds3re' expects parameter '@subid', which was not supplied

I do supply that paramter. here is the script below.

$sqlserver= "server"
$sqldatabase = "db"
$UserName="login"
$Password="password"
$dbDefine="Server=$sqlserver;Database=$sqldatabase ;Uid=$UserName;Password=$Password;"
$DBConnection=New-Object System.Data.SqlClient.SqlConnection
$DBCommand=New-Object System.Data.SqlClient.SqlCommand
$DBCommand.Connection = $DBConnection
$DBCommand.commandtext = "[sp_insertmds3re]"
$DBCommand.CommandType = [System.Data.CommandType]::StoredProcedure

$DBCommand.Parameters.Add("`'1`'",[system.Data.SqlDbType]::Int) | Out-Null
$DBCommand.Parameters.Add("`'5/1/2010 12:00:00 AM`'",[System.Data.SqlDbType]::SmallDateTime) |Out-Null
$DBCommand.Parameters.Add("`'Name`'",[system.Data.SqlDbType]::VarChar,20)| Out-Null
$DBCommand.Parameters.Add("`'1`'",[system.Data.SqlDbType]::SmallInt) | Out-Null
$DBCommand.Parameters.Add("`'2`'",[system.Data.SqlDbType]::SmallInt)| Out-Null
$DBCommand.Parameters.Add("`'3`'",[system.Data.SqlDbType]::SmallInt)| Out-Null
$DBCommand.Parameters.Add("`'Completed`'",[system.Data.SqlDbType]::VarChar,20)| Out-Null
$DBConnection.ConnectionString=$dbDefine
$DBConnection.Open()

$DBCommand.ExecuteNonQuery()

$DBConnection.Close()