CODEWITHSUNDEEP
c#sql-serverado.net
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

ADO Exception Operand type clash

Hi I'm getting a strange error need another set of eyes

I have this code:

    con.Open();
    var cmd = new SqlCommand("INSERT INTO [lntmuser].[attach] ([sysid],[name],[att_size],[cid],[flags],[contents]) VALUES (@sysid, @name, @att_size, @cid, @flags, @contents)", con) { CommandType = CommandType.Text };
    (Blah, Blah, ASIGN FIRST 5 VALUES, These All work)
    cmd.Parameters.AddWithValue("@contents", SqlDbType.Image).Value = aa.contents ?? (object) DBNull.Value;

Where aa.contents is of type byte[]. At first tried without 

"?? (object) DBNull.Value;"

and I got a "insert expects @contents parameter" so I added the ?? (object) DBNull.Value; and now I get the following ugly error when I run my unit test:

RestoreTest.TestMultiParAttLookUp : FailedSystem.Data.SqlClient.SqlException : Operand  type clash: nvarchar is incompatible with image
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean  breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,  SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject  stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,   RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,  RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at bcDAL.RestoreFunc.ExecuteSQL(List`1 emailIds, List`1 attIds, String conStrA, String  conStrM) in RestoreFunc.cs: line 173
at bcDAL.RestoreFunc.RestoreEntry(String sysid, String conStrA, String conStrM) in  RestoreFunc.cs: line 22
at tcUnitTests.RestoreTest.TestMultiParAttLookUp() in RestoreTest.cs: line 18

Where does nvarchar have come in to play ? I was looking at this question however I am explicitly stating the SQL Datatype before I assign the value.

I might just need some sleep lol. Thanks in advance.

Upvotes: 2

Views: 1723

Answers (1)

p.campbell
p.campbell

Reputation: 100607

The issue here is that ADO.NET can't tell what type DBNull.Value is, as mentioned in your referenced question. Like the answer says, ADO.NET can't tell what datatype it is, so it defaults to one.

It's not as pretty, and doesn't follow the same convention as the others, but it'll get the job done. Suggest putting this on 2+ lines instead of the single line that you've got.

var contentsParam = new SqlParameter("@contents", SqlDbType.Image);
contentsParam.Value = aa.contents ?? (object) DBNull.Value;
cmd.Parameters.Add(contentsParam);   
cmd.ExecuteNonQuery();

Upvotes: 2

Related Questions