Null Reference Exception in SQL Query output

Question

After struggling with pulling certain info from a SQL Database to use in C# code to do some permission checking, I have decided to go back to basics and learn how to do it properly and slowly build on my skills so I can eventually do what I need to do. The object of the below code is to grab the current Active Directory user, then go into a SQL table that contains usernames and other info, find one that matches and then display it in a MessageBox.

string currentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["HSEProjRegConnectionString1"].ConnectionString))
using (var cmd = new SqlCommand())
{
    cn.Open();
    cmd.Connection = cn;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT username FROM [tbl_Person] WHERE [username] LIKE '@currentUser'";

    SqlParameter param = new SqlParameter();
    cmd.Parameters.Add("@currentUser", SqlDbType.Char).Value = currentUser;

    MessageBox.Show("{0}", Convert.ToString((int)cmd.ExecuteScalar()));

    cn.Close();
}

Something is wrong though. I get the following error and Stack Trace. Line 38 is the MessageBox line.

System.NullReferenceException: Object reference not set to an instance of an object.

[NullReferenceException: Object reference not set to an instance of an object.]
HSE_project_Register.admin.Page_Load(Object sender, EventArgs e) in S:\IT\Development\Visual Studio Projects\HSE Project Register\admin\admin.aspx.cs:38
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +25
System.Web.UI.Control.LoadRecursive() +71
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3064

What is causing the Null Reference Exception in that code? If the answer is in the Stack Trace, could someone please explain that to me? I find the Stack Trace extremely confusing.

Answer 1

You need to change your query to something like

SELECT username FROM [tbl_Person] WHERE [username] LIKE '%' + @currentUser + '%'

Also, you are not getting a casting exception, but a null reference exception.

So you need to tell us what is at line 38 of your page admin.aspx.cs?

Why dont you change the query to something like

SELECT COUNT(username) as Cnt_UserName 
FROM [tbl_Person] 
WHERE [username] LIKE '%' + @currentUser + '%'

Which should always return an Int

I think the issue is with the type SqlDbType.Char. Try changing that to SqlDbType.VarChar.

Have a look at the following demo

SQL Fiddle DEMO

From LIKE (Transact-SQL)

A string comparison using a pattern that contains char and varchar data may not pass a LIKE comparison because of how the data is stored.

Answer 2

problem is with your sql statement

LIKE '@currentUser'"

this will not identified as parameter you better do as below

cmd.CommandText = "SELECT username FROM [tbl_Person] WHERE [username] LIKE @currentUser";

and when you set parameter

 cmd.Parameters.AddWithValue("@currentUser", '%' + currentUser + '%');

Answer 3

You're getting this exception because the value returned from:

(int)cmd.ExecuteScalar()

is in fact null. However, what interests me the most here is this. Your query says:

SELECT username FROM [tbl_Person]...

but then you're taking the results of that and trying to cast it to an int. It doesn't feel right to me. It seems this Convert.ToString((int)cmd.ExecuteScalar()) should actually be this cmd.ExecuteScalar() as string. This would both handle null's as well as cast its type properly.

And to fix your query, consider the following code:

cmd.CommandText = "SELECT username FROM [tbl_Person] WHERE [username] LIKE @currentUser";

cmd.Parameters.AddWithValue("@currentUser", "%" + currentUser + "%";