Why does CONVERT() allow me to use parameterized query in this case?

Question

During the development of an windows mobile application we used the following code:

const string sqlString2 = 
   @"INSERT INTO WorkOrderComment
   (WOInstructionID,WorkOrderCommentID,ClientChange,Void,UserID,VoidTimestamp) 
   (SELECT WOInstructionID, WOInstructionID, 1, @VoidBit,@VoidUser ,@VoidTimeStamp 
    FROM WorkOrderInstruction 
    WHERE InstructionGroupQuestion = 0 AND InstructionGroupNumber = @insGroupNo AND WorkOrderID=@WoID)";
var sqlcom2 = new SqlCeCommand();
sqlcom2.CommandType = CommandType.Text;
sqlcom2.CommandText = sqlString2;
sqlcom2.Parameters.Add("@VoidBit", action);

sqlcom2.Parameters.Add("@VoidUser", DBNull.Value);
sqlcom2.Parameters.Add("@VoidTimeStamp", DBNull.Value);

sqlcom2.Parameters.Add("@WoID", workOrderID);
sqlcom2.Parameters.Add("@insGroupNo", instructionGroupNumber);
sqlcom2.ExecuteNonQuery();

When running this with I receive the following error on ExecuteNonQuery: The conversion is not supported. [ Type to convert from (if known) = int, Type to convert to (if known) = uniqueidentifier ]

Even if I add sqlcom2.Parameters["@VoidUser"].DbType = DbType.Guid; I got the same error. After a while I found that this could be helped with using CONVERT. I changed @VoidUser to CONVERT(uniqueidentifier,@VoidUser).

This resulted in the following error: A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement.

After a couple of hours of reading documentation and other posts on why I could not use variables at these places I just tried to put in CONVERT everywhere; changing @VoidBit to CONVERT(bit,@VoidBit) and @VoidTimeStamp to CONVERT(datetime,@VoidTimeStamp).

And for some reason it works.

The table is created with this command:

CREATE TABLE WorkOrderComment (  WorkOrderCommentID uniqueidentifier NOT NULL CONSTRAINT WorkOrderCommentPK Primary Key,
WOInstructionID uniqueidentifier NOT NULL,
WorkOrderID_Update uniqueidentifier NULL,
TextData NTEXT NULL,
Value float NULL,
Category nvarchar(50) NULL,
BarcodeScanned bit NULL,
Timestamp DateTime NULL,
Time float NULL,
UserID uniqueidentifier NULL,
Void bit NULL,
VoidTimeStamp DateTime NULL,
FaultComplaintID uniqueidentifier NULL,
ClientChange bit NOT NULL)

Why am I not allowed to use "naked" parameters?

Why did it throw an error on the conversion in the first place, and not the usage of a parameter?

Answer 1

The Parameters.Add() method takes 2 parameters: Parameter name and data type. It seems you are confusing with Parameters.AddWithValue()

Also SQLCE seems to be more difficult with type conversion than SQL Server. So the best is you specifically mention the type of the parameters. (it only really matters for uniqueidentifier though)

I would slightly rewrite the code like this as well:

        using (var sqlConn = new SqlCeConnection(connStr))
        using (var sqlCmd = new SqlCeCommand(sqlString2, sqlConn))
        {
            sqlConn.Open();

            sqlCmd.Parameters.Add("@VoidBit", SqlDbType.Bit).Value = action;
            sqlCmd.Parameters.Add("@VoidUser", SqlDbType.UniqueIdentifier).Value = DBNull.Value;
            sqlCmd.Parameters.Add("@VoidTimeStamp", SqlDbType.DateTime).Value = DBNull.Value;
            sqlCmd.Parameters.Add("@WoID", SqlDbType.UniqueIdentifier).Value = workOrderID;
            sqlCmd.Parameters.Add("@insGroupNo", SqlDbType.Int).Value = instructionGroupNumber;

            sqlCmd.ExecuteNonQuery();
        }

See https://gist.github.com/1932722 for full repro script