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

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

Insert NULL value from DropDownList

Hello Experts,

Is there a way that I can Insert a NULL value for an int field in my DB from a DropDownList Control? For Example, I'm retrieving values from my DB into my DropDownList Control. The value labeled -- Select -- has an ID of 300. What I would like to do is if a user chooses to NOT make a selection and leave -- Select -- in that DropDown List then when they insert the Form i can put a NULL value for it's place instead of 300. Is this possible? if so, can someone assist me with my code below?

Thanks in advance!!!
protected void btn_InsertCourseSchedule_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdInsertCourseSchedule = new SqlCommand();
            cmdInsertCourseSchedule.CommandText = "HealthCourses_InsertCourseSchedule";
            cmdInsertCourseSchedule.CommandType = CommandType.StoredProcedure;
            cmdInsertCourseSchedule.Connection = conn;

            cmdInsertCourseSchedule.Parameters.Add("@ghaoc_id", SqlDbType.Int).Value = ddlCourseName.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@ghaocp_id", SqlDbType.Int).Value = ddlCourseProgram.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@hmi_id", SqlDbType.Int).Value = ddlInstructor.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@csch_startdate", SqlDbType.DateTime).Value = txtDate.Text;
            cmdInsertCourseSchedule.Parameters.Add("@csch_starttime", SqlDbType.VarChar, 50).Value = txtStartTime.Text;
            cmdInsertCourseSchedule.Parameters.Add("@csch_endtime", SqlDbType.VarChar, 50).Value = txtEndTime.Text;

            try
            {
                conn.Open();

                cmdInsertCourseSchedule.ExecuteNonQuery();

                Response.Redirect("schedule_insert_success.aspx");
            }

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

            finally
            {
                conn.Close();
            }
        }
    }

Open in new window

STORED PROCEDURE:

ALTER PROCEDURE [dbo].[HealthCourses_InsertCourseSchedule]

(
@ghaoc_id int,
@ghaocp_id int,
@hmi_id int,
@csch_startdate datetime,
@csch_starttime varchar(50),
@csch_endtime varchar(50)
)

AS

INSERT HealthCourses_CourseSchedules (ghaoc_id, ghaocp_id, hmi_id, csch_startdate, csch_starttime, csch_endtime)
VALUES (@ghaoc_id, @ghaocp_id, @hmi_id, @csch_startdate, @csch_starttime, @csch_endtime)

Open in new window

0
asp_net2
Asked:
asp_net2
  • 4
  • 4
  • 3
  • +1
1 Solution
 
käµfm³d 👽Commented:
Have you tried something like this?

if (Convert.ToInt32(ddlCourseName.SelectedItem.Value) == 300)
{
    cmdInsertCourseSchedule.Parameters.Add("@ghaoc_id", SqlDbType.Int).Value = DBNull.Value;
}
else
{
    cmdInsertCourseSchedule.Parameters.Add("@ghaoc_id", SqlDbType.Int).Value = ddlCourseName.SelectedItem.Value;
}

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
NO POINTS PLEASE
Also can be done in this way:
cmdInsertCourseSchedule.Parameters.Add("@ghaoc_id", SqlDbType.Int).Value = (ddlCourseName.SelectedItem.Value == "300" ? null : ddlCourseName.SelectedItem.Value);

Open in new window

0
 
asp_net2Author Commented:
@kaufmed / @yv989c,

The .NET Jedi Knights came to my rescue again :) Thank you both...

@kaufmed,
To be 100% honest with you I thought of that, but could not think of how to program that. I did though implement what you provided but received the following error below when Inserting into my Database.

I'm attaching CODE and STORED PROCEDURE.

I also wondering if it has to do with not allowing NULL in my Stored Procedure. I did ALLOW NULLS howerver when designing that table for just that field.


protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            RetrieveCourseNames();
            RetrieveInstructors();
        }
    }

    protected void RetrieveCourseNames()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdCourseNames = new SqlCommand();
            cmdCourseNames.CommandText = "HealthCourses_RetrieveCoursesValues";
            cmdCourseNames.CommandType = CommandType.StoredProcedure;
            cmdCourseNames.Connection = conn;

            try
            {
                conn.Open();

                SqlDataReader rdrCourseNames = cmdCourseNames.ExecuteReader();

                ddlCourseName.DataSource = rdrCourseNames;
                ddlCourseName.DataValueField= "ghaoc_id";
                ddlCourseName.DataTextField= "ghaco_name";
                ddlCourseName.DataBind();
            }

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

            finally
            {
                conn.Close();
            }
        }
    }

    protected void ddlCourseName_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlCourseProgram.Items.Clear();
        ddlCourseProgram.Items.Add(new ListItem("-- Select --", "300"));

        ddlCourseProgram.AppendDataBoundItems = true;

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString);

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SelectCityTestTest";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        cmd.Parameters.AddWithValue("@ghaoc_id", ddlCourseName.SelectedItem.Value);

        try
        {
            conn.Open();
            ddlCourseProgram.DataSource = cmd.ExecuteReader();
            ddlCourseProgram.DataTextField = "ghaocp_name";
            ddlCourseProgram.DataValueField = "ghaocp_id";
            ddlCourseProgram.DataBind();

            if (ddlCourseProgram.Items.Count > 1)
            {
                ddlCourseProgram.Enabled = true;
            }
            else
            {
                ddlCourseProgram.Enabled = false;
            }
        }

        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

    protected void RetrieveInstructors()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdInstructors = new SqlCommand();
            cmdInstructors.CommandText = "HealthCourses_RetrieveInstructorValues";
            cmdInstructors.CommandType = CommandType.StoredProcedure;
            cmdInstructors.Connection = conn;

            try
            {
                conn.Open();

                SqlDataReader rdrInstructors = cmdInstructors.ExecuteReader();

                ddlInstructor.DataSource = rdrInstructors;
                ddlInstructor.DataValueField = "hmi_id";
                ddlInstructor.DataTextField = "hmi_name";
                ddlInstructor.DataBind();
            }

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

            finally
            {
                conn.Close();
            }
        }
    }

    protected void btn_InsertCourseSchedule_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdInsertCourseSchedule = new SqlCommand();
            cmdInsertCourseSchedule.CommandText = "HealthCourses_InsertCourseSchedule";
            cmdInsertCourseSchedule.CommandType = CommandType.StoredProcedure;
            cmdInsertCourseSchedule.Connection = conn;

            cmdInsertCourseSchedule.Parameters.Add("@ghaoc_id", SqlDbType.Int).Value = ddlCourseName.SelectedItem.Value;
            //cmdInsertCourseSchedule.Parameters.Add("@ghaocp_id", SqlDbType.Int).Value = ddlCourseProgram.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@hmi_id", SqlDbType.Int).Value = ddlInstructor.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@csch_startdate", SqlDbType.DateTime).Value = txtDate.Text;
            cmdInsertCourseSchedule.Parameters.Add("@csch_starttime", SqlDbType.VarChar, 50).Value = txtStartTime.Text;
            cmdInsertCourseSchedule.Parameters.Add("@csch_endtime", SqlDbType.VarChar, 50).Value = txtEndTime.Text;

            if (Convert.ToInt32(ddlCourseProgram.SelectedItem.Value) == 300)
            {
                cmdInsertCourseSchedule.Parameters.Add("@ghaocp_id", SqlDbType.Int).Value = DBNull.Value;
            }
            else
            {
                cmdInsertCourseSchedule.Parameters.Add("@ghaocp_id", SqlDbType.Int).Value = ddlCourseProgram.SelectedItem.Value;
            }

            try
            {
                conn.Open();

                cmdInsertCourseSchedule.ExecuteNonQuery();

                Response.Redirect("schedule_insert_success.aspx");
            }

            catch (Exception ex)
            {
                //ex.Message.ToString();
                lblRequiredError.Text = ex.Message.ToString();
            }

            finally
            {
                conn.Close();
            }
        }
    }

Open in new window

ALTER PROCEDURE [dbo].[HealthCourses_InsertCourseSchedule]

(
@ghaoc_id int,
@ghaocp_id int,
@hmi_id int,
@csch_startdate datetime,
@csch_starttime varchar(50),
@csch_endtime varchar(50)
)

AS

INSERT HealthCourses_CourseSchedules (ghaoc_id, ghaocp_id, hmi_id, csch_startdate, csch_starttime, csch_endtime)
VALUES (@ghaoc_id, @ghaocp_id, @hmi_id, @csch_startdate, @csch_starttime, @csch_endtime)

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello:
but received the following error below
You forgot to post the error... but I think that you are talking about your column [ghaocp_id] not allowing null values? are you sure of this?

Please double check in your table designer that your column [ghaocp_id] in the [HealthCourses_CourseSchedules] table allow nulls.
0
 
käµfm³d 👽Commented:
I don't see in your sproc where you are not permitting NULL values. I believe to reject NULLs you have to append each param definition with Not Null.
0
 
käµfm³d 👽Commented:
@yv989c
Also can be done in this way:
Passing C# null will be converted to an empty string on the database--if it's a varchar field; I'm not sure about numeric fields. To insert an actual NULL value, you have to use DBNull.Value.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@kaufmed: You are right, that does not work, indeed the parameter is not passed to the query if it has a null value, so sql server raise an exception, my bad, because the SP parameter does not has a default null value.
0
 
asp_net2Author Commented:
Hello,

Ok, the mistake was mine. I went into the Tabel in my Database and had that field checked which means "NO NULLS" allowed. Once I unchecked that to allow NULL values for that field I was able to insert with error.

Below is my Insert Code that works fine. Would either of you change what I have?


protected void btn_InsertCourseSchedule_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmdInsertCourseSchedule = new SqlCommand();
            cmdInsertCourseSchedule.CommandText = "HealthCourses_InsertCourseSchedule";
            cmdInsertCourseSchedule.CommandType = CommandType.StoredProcedure;
            cmdInsertCourseSchedule.Connection = conn;

            cmdInsertCourseSchedule.Parameters.Add("@ghaoc_id", SqlDbType.Int).Value = ddlCourseName.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@hmi_id", SqlDbType.Int).Value = ddlInstructor.SelectedItem.Value;
            cmdInsertCourseSchedule.Parameters.Add("@csch_startdate", SqlDbType.DateTime).Value = txtDate.Text;
            cmdInsertCourseSchedule.Parameters.Add("@csch_starttime", SqlDbType.VarChar, 50).Value = txtStartTime.Text;
            cmdInsertCourseSchedule.Parameters.Add("@csch_endtime", SqlDbType.VarChar, 50).Value = txtEndTime.Text;

            if (Convert.ToInt32(ddlCourseProgram.SelectedItem.Value) == 300)
            {
                cmdInsertCourseSchedule.Parameters.Add("@ghaocp_id", SqlDbType.Int).Value = DBNull.Value;
            }
            else
            {
                cmdInsertCourseSchedule.Parameters.Add("@ghaocp_id", SqlDbType.Int).Value = ddlCourseProgram.SelectedItem.Value;
            }

            try
            {
                conn.Open();

                cmdInsertCourseSchedule.ExecuteNonQuery();

                Response.Redirect("schedule_insert_success.aspx");
            }

            catch (Exception ex)
            {
                //ex.Message.ToString();
                lblRequiredError.Text = ex.Message.ToString();
            }

            finally
            {
                conn.Close();
            }
        }
    }

Open in new window

0
 
käµfm³d 👽Commented:
Is this an application that external users use? Do you really want to dump out the contents of the Exception's message to the page? The messages can often times be quite technical in nature, and they can also expose sensitive information about the structure of your application.
0
 
asp_net2Author Commented:
No, I would prefer not to. What do you suggest I do?
0
 
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
Hello,

You have to check the selectedvalue of a drop down if the selected value is
--Select-- at that time you have to pass the "" value into the database. So, it stores blank value into the database, and when you read the dropdown value at that time, check the value if blank then set the dropdown selected value to --select--.
0
 
asp_net2Author Commented:
@yv989c, kaufmed, and samirbhogayta,

Thank you all both. I'm getting ready to close this post but before I do I was wondering if you all could look at another post that I have created that is related to this one. It has to do with formatting the data I colllect here into a GridView. It would be greatly appreciated if you could help me with the formatting. Thanks in advance!!!

Post I need help with:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_27382787.html
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now