SqlCommand.ExecuteScalar returns null, but raw SQL is not

Question

I have the following code that uses the SqlClient.ExecuteScalar method to return an identifier from a table.

using (var conn = new SqlConnection(connectionString)) using (var cmdContrib = new SqlCommand("SELECT ContributorId FROM Contributor WHERE Code='" + folderSystem.ContributorCode + "'", conn)) { conn.Open(); var contribId = cmdContrib.ExecuteScalar(); } 

It originally worked, but now contribId is null. I tested SQL in the management studio after retrieving from Profiler and was returning the identifier as expected.

Then I added an additional command to get the identifier from another table (Product).
productId is not null, and contribId continues to be null.

 using (var conn = new SqlConnection(connectionString)) using (var cmdContrib = new SqlCommand("SELECT ContributorId FROM Contributor WHERE Code='" + folderSystem.ContributorCode + "'", conn)) using (var cmdTest = new SqlCommand("SELECT productId FROM Product WHERE [filename] = 'bda00001.jpg'", conn)) { conn.Open(); var contribId = cmdContrib.ExecuteScalar(); var productId = cmdTest.ExecuteScalar(); } 

I am sure that this is something obvious, and I will hit myself for not noticing this, but so far I'm at a standstill.

Answer 1

Use Profiler to confirm:

A) how many rows are returned (I suspect 0) B) What database is in C) what is its login / user context. D) what is the actual whole SQL command.

Extract this command and run it in the same database to confirm that it returns a value. If this happens, change the execution context to the one that the profiler says the connection is working and try again. If it fails (returns 0 rows), check to see if the source table (Contributor) is a view that implements row-level security.