I am having a problem with function using SqlDataReader. What I am trying to do is this, I am reading a value from the page header and passing this to my database to verify that a user has access to my application. This is done with a stored procedure to read the value that is passed in and return the users First Name. If the user exists then this value is passed back to the page and placed in a label basically to say "Welcome: User" similar to the LoginView. If when the id is passed to the stored procedure there is no value then the return is null. I then will set the "_Fname" value to read "Nope" and on the page load event do a response redirect to my access denied page.
This works fine if my id is in the table. However the problem is that when my id is not in the table it is skipping over my code to look for null value. And when it gets to the else statement I get an exception for trying to pass a null value. The problem is in the if else statement below the sdr.Read() statement.
I have included my full procedure below. I am hoping you can all help me out. Thanks in advance.
public string Username(string ntid)
//int recid = 0;
int rowsAffected = 0;
SqlParameter returnPar = new SqlParameter();
SqlConnection conn = GetConnection();
string _Fname = "";
string strCmdText = "dbo.usp_Username";
SqlCommand cmd = new SqlCommand(strCmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@User", SqlDbType.VarChar));
cmd.Parameters["@User"].Value = ntid;
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr == null)
_Fname = "Nope";
_Fname = sdr["NM_FIRST"].ToString();
catch (SqlException e)
string errMsg = e.Message;