What is the correct way to add a string to SQL Server field of type char[x]?

Question

I am writing a script in PowerShell to utilize a stored procedure in my SQL Server 2000 database. To utilize the stored procedure w/ params I use the AddWithValue() method for sqlcmd.Parameters. I am attempting to insert a string into a column with data type char of size 8. I have been playing around with the AddWithValue() method, but I keep getting one of the two following errors:

Cannot convert value "aamstest" to type "System.Char". Error: "String must be exactly one character long."
At D:\scripts\SCADAViewBulkAdd\scadaview_bulk_add.ps1:196 char:2
+ $SqlCmd.Paramters.AddWithValue("@USNAME",[char]$USNAME);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvalidCastParseTargetInvocation

You cannot call a method on a null-valued expression.
At D:\scripts\SCADAViewBulkAdd\scadaview_bulk_add.ps1:197 char:2
+ $SqlCmd.Paramters.AddWithValue("@GRNAME",$GRNAME);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Stored procedure:

CREATE PROCEDURE spAddUserToGroup 
    @USNAME char(8), @GRNAME char(8) 
AS
BEGIN
    INSERT INTO GLOBE_USER_GROUP 
    VALUES (@USNAME, @GRNAME)
END
GO

AddWithValue() called from here:

# Call SQL Stored Procedure spAddUserToGroup
function SpAddUserToGroup ($usnameParam,$grnameParam)
{
    $SqlConnection = New-Object System.DATA.SqlClient.SqlConnection;

    $SqlConnection.ConnectionString = "SERVER=server01;DATABASE=FIGDB;UID=y;PWD=y";

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;

    # Indicate working with stored procedure
    $SqlCmd.CommandType=[System.Data.CommandType]'StoredProcedure';

    # Supply name of stored procedure
    $SqlCmd.CommandText = "spAddUserToGroup";
    $SqlCmd.Connection = $SqlConnection;

    # Set stored procedure parameters
    $USNAME = $usnameParam;
    $GRNAME = $grnameParam;

    Write-Host $USNAME
    Write-Host $GRNAME

    # Add parameters to string
    $SqlCmd.Paramters.AddWithValue("@USNAME",[char]$USNAME);
    $SqlCmd.Paramters.AddWithValue("@GRNAME",$GRNAME);

    # Initialize SQL Adapter
    #$SqlAdapter = New-Object System.Data.SqlClient.SqlAdapter;
    #$SqlAdapter.SelectCommand = $SqlCmd;

    ...
}

Question: how can I utilize the stored procedure to insert a string into a column of type char[8] with PowerShell? Are my errors a result of calling AddWithValue() incorrectly or am I handling my variable types poorly?

Answer 1

New Answer

Turns out you can specify the parameter names, but you don't have to. I think your actual issue here is that you've spelled Parameter wrong, which is why SQL Isn't accepting your input.

Change your code to this, and it should work.

$SqlCmd.Parameters.AddWithValue("@USNAME",[char]$USNAME);
$SqlCmd.Parameters.AddWithValue("@GRNAME",$GRNAME);

Old Answer, Wrong

I don't think you are supposed to specify the parameter name when you call a stored procedure. Try this instead:

Change these two lines:

$SqlCmd.Paramters.AddWithValue("@USNAME",[char]$USNAME);
$SqlCmd.Paramters.AddWithValue("@GRNAME",$GRNAME);

To this:

$SqlCmd.Paramters.AddWithValue([char]$USNAME,$GRNAME)

Also, PowerShell doesn't require line ending characters, like a semicolon.