[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

How to handle NULL Value

Hello Experts,

I have attached CodeBehind and Stored Procedure. The problem that I'm having with this code is that if there is a NULL value for ghaocp_id then no data is shown for either ghaco_name or ghaocp_name. If I have a value for both ghaoc_id and ghaocp_id then all values work fine for ghaco_name and ghaocp_name.

To Recap:
It's only when a NULL value for ghaocp_id exists. If a NULL value exists for ghaocp_id then I still need to display the value for ghaco_name.
protected void RetrieveRegisterCourseInfo()
    {
        //int ghaoc_id = Convert.ToInt32(Request.QueryString["ghaoc_id"].ToString());
        //int ghaocp_id = Convert.ToInt32(Request.QueryString["ghaocp_id"].ToString());

        int ghaoc_id;
        int ghaocp_id;

        if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id) && int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id))
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "HealthCourses_RetrieveRegisterCourseInfo";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;

                cmd.Parameters.AddWithValue("@ghaoc_id", SqlDbType.Int).Value = ghaoc_id;
                cmd.Parameters.AddWithValue("@ghaocp_id", SqlDbType.Int).Value = ghaocp_id;

                try
                {
                    conn.Open();

                    SqlDataReader rdr = cmd.ExecuteReader();

                    if (rdr.Read())
                    {
                        lblCourseName.Text = "<b>Course Name:</b> " + rdr["ghaco_name"].ToString();
                        lblCourseProgram.Text = "<b>Program:</b> " + rdr["ghaocp_name"].ToString();
                    }
                }

                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
        }
    }

Open in new window

ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int = NULL
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
LEFT OUTER JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id
AND ISNULL(ocp.ghaocp_id,1) = ISNULL (@ghaocp_id,ISNULL(ocp.ghaocp_id,1))

Open in new window

0
asp_net2
Asked:
asp_net2
  • 10
  • 7
2 Solutions
 
pivarCommented:
Hi,

If your id is null use DBNull.Value in AddWithValue (which takes the value, not the type as second parameter):

cmd.Parameters.AddWithValue("@ghaocp_id", DBNull.Value);

/peter
0
 
pivarCommented:
So if you use 0 as null for the id, try

if (ghaocp_id== 0) {
  cmd.Parameters.AddWithValue("@ghaocp_id", DBNull.Value);
} else {
  cmd.Parameters.AddWithValue("@ghaocp_id", ghaoc_id);
}
0
 
asp_net2Author Commented:
Hi pivar,

Not sure what you mean. When I add the value for ghaocp_id if there is NO VALUE then yes it's added to the DB as 0, but that is not what I"m asking help for now.

What I need help with is how to handle the values ghaco_name and ghaocp_name if the value for ghaocp_id is NULL or string.empty when I retrieve data using the SqlDataReader. I'm passing two values to this page ghaoc_id and ghaocp_id. If I have a value for ghaoc_id = 308 and ghaocp_id = then I still need to display the value for ghaco_name that is related to ghaoc_id.

If a value exists for ghaoc_id which I will always have a value for it then I need to return the value for ghaco_name that is related to ghaoc_id.

If a value exists for ghaocp_id then dispay value for ghaocp_name. If a value DOES NOT EXIST for ghaocp_id then DO NOT DISPLAY value for ghaocp_name but still display value for ghaco_name since value for ghaoc_id will exist.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
pivarCommented:
OK, sorry you'll have to change

if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id) && int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id))

to
if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id)) {
int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id);
 
 But also change the AddWithValue statements.
0
 
pivarCommented:
Try the following

protected void RetrieveRegisterCourseInfo()
    {
        //int ghaoc_id = Convert.ToInt32(Request.QueryString["ghaoc_id"].ToString());
        //int ghaocp_id = Convert.ToInt32(Request.QueryString["ghaocp_id"].ToString());

        int ghaoc_id;
        int ghaocp_id;

        if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id)) {
            int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id));
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "HealthCourses_RetrieveRegisterCourseInfo";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;

                cmd.Parameters.AddWithValue("@ghaoc_id", ghaoc_id);
                cmd.Parameters.AddWithValue("@ghaocp_id", ghaocp_id);

                try
                {
                    conn.Open();

                    SqlDataReader rdr = cmd.ExecuteReader();

                    if (rdr.Read())
                    {
                        lblCourseName.Text = "<b>Course Name:</b> " + rdr["ghaco_name"].ToString();
                        if (rdr["ghaocp_name"] != DBNull.Value) {
                           lblCourseProgram.Text = "<b>Program:</b> " + rdr["ghaocp_name"].ToString();
                        }
                    }
                }

                catch (Exception ex)
                {
                    ex.Message.ToString();
                }
            }
        }
    }

Open in new window

0
 
asp_net2Author Commented:
HI pivar,

That did not work. I get an error message when I run the code. Please see error below. Also, if I remove the extra ) and still run the code then I still don't see any values for ghaco_name even though ghaoc_id = 308 and ghaocp_id =

error message:
Compiler Error Message: CS1002: ; expected

Source Error:

 

Line 71:
Line 72:         if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id)) {
Line 73:             int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id));
Line 74:             using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
Line 75:             {
 

0
 
asp_net2Author Commented:
To give you a better idea as to what I mean. PLease see the URL below. You will see that I have a value for ghaoc_id which is 308 but ghaocp_id = "nothing". if and when ghaocp_id = then I still need to dislpay lblCourseName.Text = "<b>Course Name:</b> " + rdr["ghaco_name"].ToString(); which ghaco_name is related to the value 308 from ghaoc_id.

http://localhost:60024/WellnessSite/programinfo/ghap/secure/register.aspx?ghaoc_id=308&ghaocp_id=
0
 
pivarCommented:
Yes, a cut and paste error, it should be

int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id);

Your problem is that you only show the information if both queryparameters are parseable, when "ghaocp_id=", it is not parseable and your if clause is false. So you will not enter the if block.
So my suggestion is to check if ghao_id is parseable, but not ghaocp_id (use 0 if it isn't).
0
 
pivarCommented:
Sorry, I'm reading to fast. You fixed the extra ')'.
Can you set a breakpoint at "if (rdr.Read())" to see if you get results from the procedure?
0
 
asp_net2Author Commented:
Hi pivar,

Can you show me how I should have the following below then? Please keep in mind that ghaoc_id will ALWAYS have a values assigned to it but ghaocp_id may not have a value.

>> So my suggestion is to check if ghao_id is parseable, but not ghaocp_id (use 0 if it isn't).
0
 
pivarCommented:
Also try changing your sp to

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
LEFT OUTER JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id AND ocp.ghaocp_id = @ghaocp_id
WHERE oc.ghaoc_id = @ghaoc_id
0
 
pivarCommented:
>> So my suggestion is to check if ghao_id is parseable, but not ghaocp_id (use 0 if it isn't).

That is what is done with

        if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id)) {
            int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id);

instead of

        if (int.TryParse(Request.QueryString["ghaoc_id"], out ghaoc_id)  &&      int.TryParse(Request.QueryString["ghaocp_id"], out ghaocp_id)) {

In your original code, the if block is only executed when both ghaoc_id and ghaocp_id are valid integers.
0
 
asp_net2Author Commented:
pivar,

ok, I changed my SP to what you mentioned above and added the code you supplied in 37062910 and everyting is viewing perfectly. I still don't understand what you mean about >>But also change the AddWithValue statements and what I should have them set at.
0
 
pivarCommented:
The method signature for AddWithValue is

public SqlParameter AddWithValue(
      string parameterName,
      Object value
)

You executed it with the datatype as value. And then you set the real value

cmd.Parameters.AddWithValue("@ghaocp_id", SqlDbType.Int).Value = ghaocp_id;

You only need to do this

cmd.Parameters.AddWithValue("@ghaocp_id", ghaocp_id);
0
 
asp_net2Author Commented:
Ok, that is what I have now so I don't need to change anything correct?
0
 
pivarCommented:
If you made all my suggestion, I think that is enough. Is it working now?
0
 
asp_net2Author Commented:
Thank you!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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