Link to home
Start Free TrialLog in
Avatar of kvnsdr
kvnsdr

asked on

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();
Avatar of dstanley9
dstanley9

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);
           }
       }
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
ASKER CERTIFIED SOLUTION
Avatar of mrichmon
mrichmon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kvnsdr

ASKER

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! ");
                    }
}



SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of kvnsdr

ASKER

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!");
           }