troubleshooting Question

Add DateTime based on a value from DropDownList

Avatar of asp_net2
asp_net2Flag for United States of America asked on
.NET ProgrammingC#ASP.NET
23 Comments2 Solutions386 ViewsLast Modified:
Hello Experts,

I have a web form that I need help with. I would like to be able to add the current DateTime to a field in my DB if a DropDownList value is selected as "Closed". If the value from the DropDownList value is NOT "Closed" then do not add DateTime to field in DB.

Please see my current CodeBehind below. There is no TextBox for the Completion Date. I somehow need to programatically put that value into my DB basd on the value from the DropDownList if it's "Closed".

CodeBehind:
protected void btnModifyTicket_Click(object sender, EventArgs e)
    {
        int TicketID = Convert.ToInt32(Request.QueryString["tkt_id"].ToString());

        if (Page.IsValid) // make sure all fields have data before Inserting to DB.
        {
            if (fuAttachFile.HasFile)  // a file has been uploaded
            {
                //Make sure we are dealing a .pdf file only
                string extension = Path.GetExtension(fuAttachFile.PostedFile.FileName).ToLower();
                string MIMEType = null;

                switch (extension)
                {
                    case ".pdf":
                        MIMEType = "application/pdf";
                        break;
                    case ".doc":
                        MIMEType = "application/msword";
                        break;
                    case ".xls":
                        MIMEType = "application/vnd.ms-excel";
                        break;
                    case ".txt":
                        MIMEType = "text/plain";
                        break;
                    case ".png":
                        MIMEType = "image/png";
                        break;
                    case ".jpg":
                        MIMEType = "image/jpeg";
                        break;
                    case ".jpeg":
                        MIMEType = "image/jpeg";
                        break;
                    case ".vsd":
                        MIMEType = "application/vnd.visio";
                        break;
                    default:
                        lblFileTypeError.Visible = true;
                        lblFileTypeError.Text = "PDF files only.";
                        return;
                }

                string filename = fuAttachFile.PostedFile.FileName.Split(new char[] { '\\' }).Last();
                int fileSize = fuAttachFile.PostedFile.ContentLength;

                // Allow only files less than 1,048,576 bytes (approximately 1 MB) to be uploaded.
                if ((fileSize < 1048576))
                {
                    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString);

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

                    // Load PDF InputStream into Byte array
                    byte[] imageBytes = new byte[fuAttachFile.PostedFile.InputStream.Length + 1];
                    fuAttachFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);

                    cmd.Parameters.AddWithValue("@tkt_id", SqlDbType.Int).Value = TicketID;
                    cmd.Parameters.AddWithValue("@usr_req_chg_id", SqlDbType.Int).Value = ddlUserRequestingChange.SelectedItem.Value;
                    cmd.Parameters.AddWithValue("@req_typ_id", SqlDbType.Int).Value = ddlRequestType.SelectedItem.Value;
                    cmd.Parameters.AddWithValue("@com_id", SqlDbType.Int).Value = ddlCompany.SelectedItem.Value;
                    cmd.Parameters.AddWithValue("@sts_id", SqlDbType.Int).Value = ddlStatusType.SelectedItem.Value;
                    cmd.Parameters.AddWithValue("@ownr_id", SqlDbType.Int).Value = ddlTicketOwner.SelectedItem.Value;
                    cmd.Parameters.AddWithValue("@tkt_desc", SqlDbType.VarChar).Value = txtTicketDesc.Text;
                    cmd.Parameters.AddWithValue("@tkt_resolution", SqlDbType.VarChar).Value = txtTicketResolution.Text;
                    cmd.Parameters.AddWithValue("@tkt_file", SqlDbType.Image).Value = imageBytes;
                    cmd.Parameters.AddWithValue("@tkt_filename", SqlDbType.VarChar).Value = filename;
                    cmd.Parameters.AddWithValue("@tkt_mime", SqlDbType.VarChar).Value = MIMEType;
                    cmd.Parameters.AddWithValue("@tkt_size", SqlDbType.VarChar).Value = fileSize;

                    if (string.IsNullOrEmpty(txtCompletionDate.Text))
                    {
                        cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = txtCompletionDate.Text;
                    }

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }

                    catch (Exception ex)
                    {
                        lblInsertError.Visible = true;
                        lblInsertError.Text = ("Error on insert: " + ex.Message.ToString());
                    }

                    finally
                    {
                        Response.Redirect("index.aspx");
                        conn.Close();
                    }
                }

                else
                {
                    lblFileSize.Visible = true;
                    lblFileSize.Text = "File size must be 1MB or smaller.";
                }
            }
            else  // no file has been uploaded, we only need to update txtPhysicalDateCompleted
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CMDB"].ConnectionString);

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

                byte[] imageBytes = new byte[1];

                cmd.Parameters.AddWithValue("@tkt_id", SqlDbType.Int).Value = TicketID;
                cmd.Parameters.AddWithValue("@usr_req_chg_id", SqlDbType.Int).Value = ddlUserRequestingChange.SelectedItem.Value;
                cmd.Parameters.AddWithValue("@req_typ_id", SqlDbType.Int).Value = ddlRequestType.SelectedItem.Value;
                cmd.Parameters.AddWithValue("@com_id", SqlDbType.Int).Value = ddlCompany.SelectedItem.Value;
                cmd.Parameters.AddWithValue("@sts_id", SqlDbType.Int).Value = ddlStatusType.SelectedItem.Value;
                cmd.Parameters.AddWithValue("@ownr_id", SqlDbType.Int).Value = ddlTicketOwner.SelectedItem.Value;
                cmd.Parameters.AddWithValue("@tkt_desc", SqlDbType.VarChar).Value = txtTicketDesc.Text;
                cmd.Parameters.AddWithValue("@tkt_resolution", SqlDbType.VarChar).Value = txtTicketResolution.Text;
                cmd.Parameters.AddWithValue("@tkt_file", SqlDbType.Image).Value = imageBytes;
                cmd.Parameters.AddWithValue("@tkt_filename", SqlDbType.VarChar).Value = "";
                cmd.Parameters.AddWithValue("@tkt_mime", SqlDbType.VarChar).Value = "application/octet-stream";
                cmd.Parameters.AddWithValue("@tkt_size", SqlDbType.VarChar).Value = 0;

                if (string.IsNullOrEmpty(txtCompletionDate.Text))
                {
                    cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = DBNull.Value;
                }
                else
                {
                    cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = txtCompletionDate.Text;
                }

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }

                catch (Exception ex)
                {
                    lblInsertError.Visible = true;
                    lblInsertError.Text = ("Error on insert: " + ex.Message.ToString());
                }

                finally
                {
                    Response.Redirect("index.aspx");
                    conn.Close();
                }
            }
        }
    }
ASKER CERTIFIED SOLUTION
Rouchie

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 23 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros