2

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.

Community
  • 1
  • 1
bumble_bee_tuna
  • 3,533
  • 7
  • 43
  • 83

1 Answers1

2

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();
p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • It works but to my sleepy eye what's the difference other then the syntax ?? – bumble_bee_tuna Apr 17 '12 at 03:45
  • Suspect that `AddWithValue().Value=` does it all in one shot: it sets the type along with the value, and ensures they match. If value doesn't match the type that you've specifies, then it downgrades to something that does match. Whereas here in my answer, you're being explicit, and setting the type when creating the object. I don't have the real answer, we'd have to look at the ADO.NET source to really be sure. – p.campbell Apr 17 '12 at 03:55