SqlCommand.ExecuteScalar returns null but raw SQL does not

Question

I have the following code that uses the SqlClient.ExecuteScalar method to return an ID 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();
}

Originally it was working but now contribId is null. I tested the SQL in management studio after extracting from Profiler and it returned the ID as expected.

Next I added an additional command to retrieve an ID from a different table (Product).
productId is not null while 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 it is something obvious and I'll kick myself for not noticing it, but for now I'm stumped.

Fingerstall

What's your debug value for folderSystem.ContributorCode? – Gotha

What value does folderSystem.ContributorCode contain? I'm leaning towards this being an SQL-injection sort of bug (eg a single-quote in the variable's value). – Byte

Construct cmdContrib with a static SQL query, like you have with cmdTest, and see what happens. The problem might jump out at you then. – Secular

folderSsystem.ContributorCodde contains 'PST'. – Fingerstall

The debug value for folderSystem.ContributorCode is "PST". Tried making it a static query: "SELECT ContributorId FROM tblContributor WHERE Code='PST'". Still returns null. – Fingerstall

Answer 1

Use Profiler to confirm:

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

Extract this command and re-execute it in the same database to confirm that it does return a value. If this suceeds, then change your execution context to that which the Profiler said that the connection was running under and try again. If it fails now (returns 0 rows) then check to see if the source table (Contributor) may actually be a View that is implementing row-level security.

Discern

" C) what its login/user context" +1 to that, it's very easy that the app is running in a different context – Ciborium

It was a different database (embarrassing) Although this answer can serve as a check list for this category of question. – Fingerstall

Don't worry, it happens to all of us sooner or later. – Discern