• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

C# And SQL DB Result Null Return Error?

I have a code that works well as long as the DB has a result,  otherwise C# errors. I use SQL Query extensively to test all SQL scripts and under certain conditions it will return just a header column and NO rows. This is understandable, however I'm not sure how C# understands the DB return. Here's the code that works for a none null data return.

SqlDataReader dr = da1.SelectCommand.ExecuteReader();

                while (dr.Read())
                {
                    if (dr.HasRows)
                    {
                        strUserName = dr.GetString(0);
                    }
                    else if (!dr.HasRows)
                    {
                        strUserName = "";
                    }
                }
                return strUserName.ToString();
0
kvnsdr
Asked:
kvnsdr
2 Solutions
 
dstanley9Commented:
If you execute a query that returns a null value, HasRows will be true, so that doesn't work, but the following should work:

     while (dr.Read())
       {    
          if (dr.IsDBNull(0)
          {
            strUserName = "";
          }
          else
          {
             strUserName = dr.GetString(0);
           }
       }
0
 
neilpriceCommented:
If you're using c# 2.0 you can also look into nullable types...

string? strUserName;

Then the strUserName variable can legally be NULL aswell.

Hope this helps,
Neil
0
 
mrichmonCommented:
You can also just do

strUserName  = dr["columnname"].ToString();

If it is null it will return an empty string and not error like the GetString does.

WOrks in .NET 1.1 or .NET 2

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
kvnsdrAuthor Commented:
I've tried all your suggestions but nothing is working.

As I mentioned, my code works well as long as there's some data......

Here's more project info.......  It's a Web Service, but a DB return is still a DB Return......... I think?


[WebMethod]
 public String SendUserInfo()
{
    // SQL code
                SqlDataReader dr = da1.SelectCommand.ExecuteReader();

                while (dr.Read())
                {
                    if (dr.HasRows)
                    {
                        strUserName = dr.GetString(0);
                    }
                    else
                    {
                        strUserName = "";
                    }
                }
                return strUserName.ToString();
}
-------------------------------------------------------------------

Windows Application:

Private void GetUserInfo()
{
            String sUserName = service.SendUserInfo();
           
                if (sUserName != "")
                    {
                        // Write locally
                    }
                        MessageBox.Show("No User Info Found! ");
                    }
}



0
 
pallospCommented:
Line if (dr.HasRows) is unnecessary, because if dr.Read() returned true, it had successfully read a row.
If the user name can be null, you can't convert it to string directly. First you should compare dr[0] by DBNull.Value
The last problem is that strUserName is not initialized if the table doesn't contain rows.
0
 
jimbobmcgeeCommented:
It may be better if, in your web service, you have an instansiated string that is altered by your Read loop and then returned by the method, such as:

            [WebMethod]
            public String SendUserInfo()
            {
                // SQL code
                SqlDataReader dr = da1.SelectCommand.ExecuteReader();
                string szOutput = string.Empty;

                while (dr.Read())
                {
                    try
                    {
                        if (!dr.IsDBNull(0)) szOutput = dr.GetString(0);
                    }
                    catch {}
                }

                return szOutput;
            }

Then, in your app, you can check for this:

        Private void GetUserInfo()
        {
            string sUserName = service.SendUserInfo();  
            if (sUserName != string.Empty)
            {
                // Write locally
            }
            else
                MessageBox.Show("No User Info Found!");
        }

HTH

J.
0
 
kvnsdrAuthor Commented:
Thank you all for your help in solving this delima............

Ta Da, and the answer is:

[WebService]

while (dr.Read())
                {                                      
                        strUserName = dr["col_UserName"].ToString();
               }
                return strUserName;

-------------------------------------------------------------------

Win App:

 string sUserName = service.SendUserInfo();  
            if (sUserName == True)
            {
                // Write locally
            }
            else
           {
                MessageBox.Show("No User Info Found!");
           }
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now