object reference error and stored procedure

This has been a fun day, let me tell you. Anyway, I have a helper class that handles my database calls (ok, 3 of them, but DatabaseAccess is the main one) and its used as such from within the global.ascx file... for reference Utilities.UserName just strips out the <DOMAIN> part of the username (I'm using windows authen) and GetMyDepartments executes a scalar command and returns a string.

1void Session_Start(object sender, EventArgs e)
2 {
3string userName = Utilities.UserName(Request.LogonUserIdentity.Name);
4string deptID = DatabaseAccess.GetMyDepartment(userName);
56 Session.Add("UserName", userName);
7 Session.Add("DeptID", deptID);
89if (deptID ==string.Empty)
10 {
11 Response.Redirect("setup.aspx");
12 }
13 }

The problem happens at line 4. If a user has a department ID, its happy and goes about its business with no problem ...but as soon as it doesn't (from, say, a new user that hasn't accessed the site) it throws a nasty Object reference not set to an instance of an object. And I have -no- clue why. I've tried dropping it into page_loads and even profiles but no help. So what am I overlooking?

If you traced GetMyDepartment() you'd see that when it fails, it fails to return a proper string. The object (a DataSet maybe?) doesn't get created. Make sure that if it fails, it exits gracefully returning String.Empty which is what you are checking for in the returned value.

The object -should- end up as a string (since execute scalar returns column[0] cell[0]) -- and this works for 5 or 6 other scalars commands as well which is why its confusing me to no end. Maybe this'll help some more ...the code behind the dbaccess looks something like this

1public static string GetMyDepartment(string userName)2 {3 DbCommand comm = DataAccess.CreateCommand();4 comm.CommandText ="GetMyDepartment";5 comm.Parameters.Add(DatabaseParameter.Str(comm,"@.UserName", userName, 25));6return DataAccess.ExecuteScalar(comm);7 }

1public static string ExecuteScalar(DbCommand command)2 {3string value =string.Empty;4try5 {6 command.Connection.Open();7value = command.ExecuteScalar().ToString();8 }9catch (Exception ex)10 {11 Utilities.LogError(ex);12throw ex;13 }14finally15 {16 command.Connection.Close();17 }18return value;19 }

The problem comes from the executescalar.

on line 7 in ExecuteScalar, command.ExecuteScalar() will return null when the resultset is empty.Applying ToString() will raise an error that you log ...but throw again. This error will be propagated to your initial calling function in global.asax.

Got it. I switched out the datatype from string to object (since thats what ExecuteScalar() returns anyway) and check for a null value before returning that value as such...

1public static string ExecuteScalar(DbCommand command)2 {3object value =new object();4try5 {6 command.Connection.Open();7value = command.ExecuteScalar();8 }9catch (Exception ex)10 {11 Utilities.LogError(ex);12throw ex;13 }14finally15 {16 command.Connection.Close();17 }1819if (value ==null)20 {21value =string.Empty;22 }23return value.ToString();24 }

works like a charm. Thanks.