asked on

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

Alfred A.

Hi,

use IsDBNull function to deal with nulls.

Alfred A.

Hi,

Just additional comment, use "Convert.IsDBNull" function.

woodje

ASKER

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)

woodje

ASKER

I have also tried.

if (sdr["NM_FIRST"] != Convert.IsDBNull)

Alfred A.

Hi,

here is an example

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

}

lazyberezovsky

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

woodje

ASKER

Still getting the Invalid attempt message. I have also wrote it as

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

lazyberezovsky

woodje, you should compare with DbNull.Value see my example

Alfred A.

Hi,

Check this out.

http://msdn.microsoft.com/en-us/library/system.convert.isdbnull.aspx

lazyberezovsky

For your code:
_Fname = (sdr["NM_FIRST"] == DbNull.Value) ? null : (string)sdr["NM_FIRST"];

With helper:
_Fname = helper.ToString(sdr["NM_FIRST"]);

woodje

ASKER

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

Alfred A.

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())
{

}

Alfred A.

Hi,

Oh by the way, the DR should be sdr. :-)

Alfred A.

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.

lazyberezovsky

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();
}

woodje

ASKER

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.

Alfred A.

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

ASKER CERTIFIED SOLUTION

lazyberezovsky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

woodje

ASKER

lazyberezovsky,

No change with your code either.

lazyberezovsky

What's the error?

Alfred A.

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?

Alfred A.

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

lazyberezovsky

BTW why don't you use ExecuteScalar instead of using reader?

woodje

ASKER

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?

lazyberezovsky

add using System;
or use System.DbNull.Value

Alfred A.

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.

woodje

ASKER

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.

Alfred A.

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.

Alfred A.

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.

woodje

ASKER

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?)"

woodje

ASKER

lazyberezovsky,

Never mind I had a typo I was using system. instead of System. I hate when I do that. LOL

woodje

ASKER

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

lazyberezovsky

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.

lazyberezovsky

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 :)