Conversion failed when converting from a string to a uniqueidentifier

Question

When I run my code on debug I get this error:

Conversion failed when converting from a string to a uniqueidentifier

Here is the code:

public class UserObject
{
    private string m_name = string.Empty;

    public UserObject(string id)
    {
    #region Internal Logic
        try
        {
            using (SqlConnection cn = new SqlConnection(SiteConfig.ConnectionString))
            {
                string sSQL = "SELECT [UserName] FROM [aspnet_users] WHERE [UserID] = @UserID";
                using (SqlCommand cm = new SqlCommand(sSQL, cn))
                {
                    cm.Parameters.AddWithValue("@UserID", id);
                    cn.Open();
                    using (SqlDataReader rd = cm.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            m_name = rd[0].ToString();
                        }
                        rd.Close();
                    }
                    cn.Close();
                }
            }
        }
        catch (Exception ex)
        {

        }
    #endregion Internal logic
    }
}

Answer 1

You said in your comment to the question that id does not have a value when being passed into the method. From the database point of view, uniqueidentifiers can be null (DBNull in C#), but to achieve this, you'd have to leave out the parameter or set DBNull.Value explicitly.

In C#, Guid can not be null - so you must either provide Guid.Empty or a string that can be converted to a Guid in the call to AddWithValue.

EDIT
Sample code follows: Please note that given the SQL statement you use, you won't get any results for the Guid.Empty case unless you have a user the ID of which contains only 0s. I suggest, you change the where clause of your SQL statement as follows:

WHERE [UserId] = ISNULL(@UserID, [UserId])

That way, you get all users when you pass null.

public UserObject(string id)
{
    try
    {
        using (SqlConnection cn = new SqlConnection(SiteConfig.ConnectionString))
        {
            string sSQL = "SELECT [UserName] FROM [aspnet_users] WHERE [UserID] = @UserID";
            using (SqlCommand cm = new SqlCommand(sSQL, cn))
            {

                if (id.Length == 0)
                    cm.Parameters.AddWithValue("@UserID", Guid.Empty);
                else if (id == null)
                    cm.Parameters.AddWithValue("@UserID", DBNull.Value);
                else
                    cm.Parameters.AddWithValue("@UserID", Guid.Parse(id));

                cn.Open();
                using (SqlDataReader rd = cm.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        m_name = rd[0].ToString();
                    }
                    rd.Close();
                }
                cn.Close();
            }
        }
    }
    catch (Exception ex)
    {

    }