SqlDataReader and Null value problems

Hello Experts,

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.

Jeff
public string Username(string ntid)
    {
        //int recid = 0;
        int rowsAffected = 0;
        SqlParameter returnPar = new SqlParameter();

        SqlConnection conn = GetConnection();

        string _Fname = "";

        try
        {
            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;
            
            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
            sdr.Read();
            
            if (sdr == null)
            {
                _Fname = "Nope";
            }
            else
            {
                _Fname = sdr["NM_FIRST"].ToString();
            }
        }
        catch (SqlException e)
        {
            string errMsg = e.Message;
        }
        finally
        {            
            conn.Close();
        }
        
        return _Fname;        
    }

Open in new window

woodjeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alfred A.Commented:
Hi,

use IsDBNull function to deal with nulls.
0
Alfred A.Commented:
Hi,

Just additional comment, use "Convert.IsDBNull" function.
0
woodjeAuthor Commented:
Alfred1,

Thanks for the quick response I have tried using this code for the if statement all I get then is an exception "Invalid attempt to read when no data is present."

            if (sdr["NM_FIRST"] != Convert.DBNull)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

woodjeAuthor Commented:
I have also tried.

if (sdr["NM_FIRST"] != Convert.IsDBNull)
0
Alfred A.Commented:
Hi,

here is an example

 if(Convert.IsDBNull(sdr["NM_FIRST"]))
      {

      }
0
lazyberezovskyCommented:
IsDbNull function also exist in DataReader itself.
But I'd advice you to create helper class to make those conversion. E.g:
public int ToInt32(object value)
{
 if (value is Int32) return (Int32)value;
 if ((value == null) || (value == DbNull.Value))
     return default(Int32);
  // other actions if needed
}

Open in new window

0
woodjeAuthor Commented:
Still getting the Invalid attempt message. I have also wrote it as

if (Convert.IsDBNull(sdr["NM_FIRST"].ToString()))
0
lazyberezovskyCommented:
woodje, you should compare with DbNull.Value see my example
0
Alfred A.Commented:
0
lazyberezovskyCommented:
For your code:
 _Fname =  (sdr["NM_FIRST"] == DbNull.Value) ? null : (string)sdr["NM_FIRST"];

With helper:
_Fname = helper.ToString(sdr["NM_FIRST"]);
0
woodjeAuthor Commented:
Alfred1,

I read the article it by what it is saying this code should be working. However it is not.

lazyberezovsky,

I am not using the helper class. Sorry but I tried the code you had listed on both my original and the code I have now and I am still getting the Invalid attempt error.

All I am going to include the code I have setup now. So we are all on the same page.
if (Convert.IsDBNull(sdr["NM_FIRST"]))
            {
                _Fname = "Nope";
            }
            else
            {
                _Fname = sdr["NM_FIRST"].ToString();
            }

Open in new window

0
Alfred A.Commented:
Hi woodje,

This might be a case where no rows where returned.

if you do this,

DR.Read();  //and no rows returned.

if(Convert.IsDBNull(sdr["NM_FIRST"]))  //you will get "Invalid attempt to read when no data is present."
      {

      }

You should check this using a while

while(DR.Read())
{

}

0
Alfred A.Commented:
Hi,

Oh by the way,  the DR should be sdr. :-)
0
Alfred A.Commented:
Hi,

Oh, after reviewing your code snippet, there is no checking of the Read function of the data reader.

You should use while in each Read.
0
lazyberezovskyCommented:
Sorry, thought you are getting string from db. Fixed:

if (Convert.IsDBNull(sdr["NM_FIRST"]) || (sdr["NM_FIRST"] == null))
 {
                _Fname = "Nope";
 }
 else
 {
                _Fname = sdr["NM_FIRST"].ToString();
 }
0
woodjeAuthor Commented:
Still no go I entered while(sdr.Read()); and left all the rest of our code and I still get back to the Invalid attempt.
0
Alfred A.Commented:
Hi,

Just to be clear of what I was saying about the Read function, I modified your code snippet.
public string Username(string ntid)
    {
        //int recid = 0;
        int rowsAffected = 0;
        SqlParameter returnPar = new SqlParameter();

        SqlConnection conn = GetConnection();

        string _Fname = "";

        try
        {
            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;
            
            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
            while(sdr.Read())
            {
                if(Convert.IsDBNull(sdr["NM_FIRST"])) 
                {
                    _Fname = "Nope";
                }
                else
                {
                    _Fname = sdr["NM_FIRST"].ToString();
                }
            }
            sdr.Close();
        }
        catch (SqlException e)
        {
            string errMsg = e.Message;
        }
        finally
        {            
            conn.Close();
        }
        
        return _Fname;        
    }

Open in new window

0
lazyberezovskyCommented:
Use this.
public string Username(string ntid)
{
    using (SqlConnection conn = GetConnection())
    {
        string usernameCommandText = "dbo.usp_Username";
        SqlCommand cmd = new SqlCommand(usernameCommandText, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@User", ntid);


        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            object name = reader["NM_FIRST"];

            if ((name != DbNull.Value) && (name != null))
                return name.ToString();
        }
    }

    return "Nope";
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
woodjeAuthor Commented:
lazyberezovsky,

No change with your code either.
0
lazyberezovskyCommented:
What's the error?
0
Alfred A.Commented:
Hi,

OK.  What is the status of sdr after doing a cmd.ExecuteReader?  Does it have records in it or nothing?

And if there are records, can you put a breakpoint on while(sdr.Read()) and then jump to the next line to see if it is reading the result properly?
0
Alfred A.Commented:
Hi,

Check this article also about,

Invalid attempt to read when no data is present.

http://bytes.com/topic/asp-net/answers/286045-invalid-attempt-read-when-no-data-present
0
lazyberezovskyCommented:
BTW why don't you use ExecuteScalar instead of using reader?
0
woodjeAuthor Commented:
lazyberezovsky,

I replaced my code with yours to try it out.  However The DbNull is showing a syntax error "DbNull doesn't exsist in the current context." What reference do I need to add to get this to register?
0
lazyberezovskyCommented:
add using System;
or use System.DbNull.Value
0
Alfred A.Commented:
Hi,

Just a comment regarding @lazyberezovsky's use of "if" rather than "while",  you can use "if" if you are just dealing with one scalar value but if you have multiple results then you need to use "while" to loop through the results.  Just to be clear.
0
woodjeAuthor Commented:
Alfred1,

If the the stored procedure finds a record then I get something like this NM_FIRST = Jeffrey if the procedure doesn't find anything the it shows NM_FIRST nothing ( all it gives is the row header.
0
Alfred A.Commented:
Hi,

If the stored procedure returns nothing then there are no rows!

The "If" example of @lazyberezovsky or "while" example should have resolved that issue.
0
Alfred A.Commented:
Hi,

If there is nothing to read in the datareader, sdr.Read() should have a boolean value of "false" and it would not continue the process inside the if or while.
0
woodjeAuthor Commented:
lazyberezovsky,

I changed it to System.DbNull.Value. And my error says "Error      5      The type or namespace name 'DbNull' does not exist in the namespace 'System' (are you missing an assembly reference?)"
0
woodjeAuthor Commented:
lazyberezovsky,

Never mind I had a typo I was using system. instead of System. I hate when I do that. LOL
0
woodjeAuthor Commented:
Here is the end result code.
public string Username(string ntid)
    {
        using (SqlConnection conn = GetConnection())
        {
            try
            {
                string usernameCommandText = "dbo.usp_Username";
                SqlCommand cmd = new SqlCommand(usernameCommandText, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@User", ntid);


                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    object name = reader["NM_FIRST"];

                    if ((name != System.DBNull.Value) && (name != null))
                        return name.ToString();
                }
            }
            catch (SqlException e)
            {
                string errMsg = e.Message;
            }
            finally
            {            
                conn.Close();
            }
        }
        return "Nope";
    }

Open in new window

0
lazyberezovskyCommented:
It is definitely in System:
http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

What you are telling is nearly impossible.
Anyway, you can use if (!Convert.IsDBNull(name)  && (name != null))  instead.
0
lazyberezovskyCommented:
You don't need to use

finally
{            
       conn.Close();
}

using will make this work behind the scene.
Also string errMsg = e.Message; is never used :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.