Solved

Add DateTime based on a value from DropDownList

Posted on 2012-12-20
23
329 Views
Last Modified: 2013-01-04
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();
                }
            }
        }
    }

Open in new window

0
Comment
Question by:asp_net2
  • 11
  • 10
  • 2
23 Comments
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
ID: 38711879
There is no TextBox for the Completion Date. I somehow need to programatically put that value
Please clarify:  Is there, or is there not a TextBox? If no, then do you simply want to put the current date into the DB?

As far as the DD goes, you can check for the value in such a manner:

...

if (theDdlInQuestion.SelectedItem != "Closed")
{
    cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = DBNull.Value;
}
else
{
    cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = txtCompletionDate.Text;
}

...

Open in new window

0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38712392
Rather than do this in ASP.NET, you can do it directly in the database.  For example, when the field is set to 'closed', as part of the update statement use the GetDate() function (that's in SQL):

Declare d datetime
SET d = GetDate()
UPDATE myTable SET status = 'closed', completionDate = d WHERE ID = someID
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38712834
@kaufmed,

There is no TextBox Control. I need to programatically add the DateTime if the value from the DropDownList is "Closed". If it is then I need to add DateTime to the field in the DB.

I tried to add your code but when I add it I'm receiving a red line with the following error message.

Red Line Error Message:
Operator '!=' cannot be applied to operands of type 'System.Web.UI.WebControls.ListItem' and 'string'
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38712837
@Rouchie,

Can you show me how to perform that in the DB? Please see my current Stored Procedure that I'm using now for the Update.

Also, the field that I need to add the DateTime into will not always have the value 'Closed' assigned to it. The user may change that value on the webform to 4 other options so not sure how this can be done within SQL.

ALTER PROCEDURE [dbo].[ModifyTicket]

(
@tkt_id int,
@usr_req_chg_id int,
@req_typ_id int,
@com_id int,
@sts_id int,
@ownr_id int,
@tkt_desc varchar(max),
@tkt_resolution varchar(max),
@tkt_cmpl_dt datetime,
@tkt_file image,
@tkt_filename varchar(50),
@tkt_mime varchar(100),
@tkt_size varchar(50)
)

AS

UPDATE Tickets
      SET usr_req_chg_id = @usr_req_chg_id,
	        req_typ_id = @req_typ_id,
			com_id = @com_id,
			sts_id = @sts_id,
			ownr_id = @ownr_id,
			tkt_desc = @tkt_desc,
			tkt_resolution = @tkt_resolution,
			tkt_cmpl_dt = @tkt_cmpl_dt,
            tkt_file = case when DATALENGTH(@tkt_file) <= 4 then tkt_file else @tkt_file end,
            tkt_filename = isnull(nullif(@tkt_filename,''), tkt_filename),
            tkt_mime = isnull(nullif(@tkt_mime,'application/octet-stream'), tkt_mime),
            tkt_size = isnull(nullif(@tkt_size,0), tkt_size)
      WHERE tkt_id = @tkt_id

Open in new window

0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38712930
Sure - just make sure you change the name of the column I've guess at below to your actual data column name:

ALTER PROCEDURE [dbo].[ModifyTicket]

(
@tkt_id int,
@usr_req_chg_id int,
@req_typ_id int,
@com_id int,
@sts_id int,
@ownr_id int,
@tkt_desc varchar(max),
@tkt_resolution varchar(max),
@tkt_cmpl_dt datetime,
@tkt_file image,
@tkt_filename varchar(50),
@tkt_mime varchar(100),
@tkt_size varchar(50)
)

AS

DECLARE @today DATETIME /* <-- my addition */
SET @today = GetDate()  /* <-- my addition */

UPDATE Tickets
      SET usr_req_chg_id = @usr_req_chg_id,
	        req_typ_id = @req_typ_id,
			com_id = @com_id,
			sts_id = @sts_id,
			ownr_id = @ownr_id,
			tkt_desc = @tkt_desc,
			tkt_resolution = @tkt_resolution,
			tkt_cmpl_dt = @tkt_cmpl_dt,
            tkt_file = case when DATALENGTH(@tkt_file) <= 4 then tkt_file else @tkt_file end,
            tkt_filename = isnull(nullif(@tkt_filename,''), tkt_filename),
            tkt_mime = isnull(nullif(@tkt_mime,'application/octet-stream'), tkt_mime),
            tkt_size = isnull(nullif(@tkt_size,0), tkt_size),
            [DateColumnNameHere] = @today  /* <-- my addition */
      WHERE tkt_id = @tkt_id

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 38712945
@Rouchie,

Sorry, I don't understand what I need to put for [DateColumnNameHere].

Also, if i'm using the (getDate()) function in SQL do I still need to declare the @tkt_cmpl_dt on top along with the Update procedure below?
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38712957
I assumed that you had a field in your Tickets database table that held a date value.  Am I wrong with that guess?   If not, then you simply replace DateColumnNameHere with the column name that holds the date.

If I am wrong, please let me know (in laymans terms - assuming I don't know anything about your app!) where the date needs to go!
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 38712976
Red Line Error Message:
Operator '!=' cannot be applied to operands of type 'System.Web.UI.WebControls.ListItem' and 'string'
Add "Value" to that line:

if (theDdlInQuestion.SelectedItem.Value != "Closed")

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 38712992
>> I assumed that you had a field in your Tickets database table that held a date value.  Am I wrong with that guess?

Yes, the name of the field that I need to add the (getdate()) function to is called "tkt_cmpl_dt". I only need to add the (getdate()) function to "tkt_cmpl_dt" if the value of "sts_id" equals "Closed" or "72" which "72" represents "Closed" in the DB.
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38713015
Okay that's a bit trickier but still fine.  We do the logic testing earlier.

ALTER PROCEDURE [dbo].[ModifyTicket]

(
@tkt_id int,
@usr_req_chg_id int,
@req_typ_id int,
@com_id int,
@sts_id int,
@ownr_id int,
@tkt_desc varchar(max),
@tkt_resolution varchar(max),
@tkt_cmpl_dt datetime,
@tkt_file image,
@tkt_filename varchar(50),
@tkt_mime varchar(100),
@tkt_size varchar(50)
)

AS


DECLARE @today DATETIME 

IF @sts_id = 72
	SET @today = GetDate() 
ELSE
	SET @today = NULL

UPDATE Tickets SET 
	usr_req_chg_id = @usr_req_chg_id,
	req_typ_id = @req_typ_id,
	com_id = @com_id,
	sts_id = @sts_id,
	ownr_id = @ownr_id,
	tkt_desc = @tkt_desc,
	tkt_resolution = @tkt_resolution,
	tkt_cmpl_dt = @tkt_cmpl_dt,
	tkt_file = case when DATALENGTH(@tkt_file) <= 4 then tkt_file else @tkt_file end,
	tkt_filename = isnull(nullif(@tkt_filename,''), tkt_filename),
	tkt_mime = isnull(nullif(@tkt_mime,'application/octet-stream'), tkt_mime),
	tkt_size = isnull(nullif(@tkt_size,0), tkt_size),
	tkt_cmpl_dt = @today  /* <-- my addition */
WHERE
	(tkt_id = @tkt_id)

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713016
@kaufmed,

That did not work either. No errors this time but when I selected the value "Closed" from the DropDownList it added the value NULL instead of the DateTime. Please see my codebehind below for the Click Event. Notice though that I do not have a TextBox Control so I added System.DateTime.Now in it's place.

The only other thing I can think of is that "Closed" represents 72 in the DB for that value.

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 (ddlStatusType.SelectedItem.Value != "Closed")
                    {
                        cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = System.DateTime.Now;
                    }


                    //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 (ddlStatusType.SelectedItem.Value != "Closed")
                {
                    cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = DBNull.Value;
                }
                else
                {
                    cmd.Parameters.AddWithValue("@tkt_cmpl_dt", SqlDbType.DateTime).Value = System.DateTime.Now;
                }


                //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();
                }
            }
        }
    }

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 25

Expert Comment

by:Rouchie
ID: 38713026
You're logic is really a database integrity rule, so you might as well save time and just use the stored procedure, to save ASP.NET messing about with the logic!
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713040
@Rouchie,

Ok, based on the solution you supplied in post 38713015 i'm not sure that will work. You have the value sts_id equal to NULL if the value is not 72. But remember I will have up to 4 other values to add. So what do I do if the value is 70, 71, 73 and or 75? It's not jsut 72 only.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713048
@kaufmed,

Your solution worked. I just had to remove "the value Closed" and assign the value "Closed" to the value of "72" :)
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38713061
You can use a CASE statement to set the @today value based on the value of  sts_id.
Let me know all the possibilities and I'll write the CASE statement in the procedure
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713087
@Rouchie,

Can you show me? It would be nice to have this work either via code or DB.
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38713134
Yes I will show you, but first, I need to know the possibilities that your logic can include.  I could perhaps fathom this from C#, but I'd prefer it in plain English so that I don't make any mistakes please!

e.g.
value sts_id equal to NULL if the value is not 72.
value sts_id equal to ______ if value is _____
etc
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713143
sts_id possible values:

70 = -- Select --
71 = Open
72 = Closed
73 = Locked
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38713186
Just need numbers and column names please, so...

If sts_id = 72 Then tkt_cmpl_dt = (today's date)
If sts_id = ____ Then tkt_cmpl_dt = ____

if that's okay?!?
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713210
If sts_id = 72 then tkt_cmpl_dt = (todays date)

if sts_id = any other value then NULL needs to be assigned to tkt_cmpl_dt.

Thanks again Rouchie, even though kaufmed's solution worked in Code I would also like to have it work in SQL and then I can share points between you both.
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 250 total points
ID: 38713304
That's what my code does I believe, unless you can find a flaw.  See the comments:

ALTER PROCEDURE [dbo].[ModifyTicket]

(
@tkt_id int,
@usr_req_chg_id int,
@req_typ_id int,
@com_id int,
@sts_id int,
@ownr_id int,
@tkt_desc varchar(max),
@tkt_resolution varchar(max),
@tkt_cmpl_dt datetime,
@tkt_file image,
@tkt_filename varchar(50),
@tkt_mime varchar(100),
@tkt_size varchar(50)
)

AS


DECLARE @today DATETIME 		-- we will set this value then insert it into tkt_cmpl_dt

IF @sts_id = 72				-- sts_id = 72 so set @today as today's date
	BEGIN
		SET @today = GetDate() 
	END
ELSE					-- sts_id is not 72 so opt for null
	BEGIN
		SET @today = NULL
	END

UPDATE Tickets SET 
	usr_req_chg_id = @usr_req_chg_id,
	req_typ_id = @req_typ_id,
	com_id = @com_id,
	sts_id = @sts_id,
	ownr_id = @ownr_id,
	tkt_desc = @tkt_desc,
	tkt_resolution = @tkt_resolution,
	tkt_cmpl_dt = @tkt_cmpl_dt,
	tkt_file = case when DATALENGTH(@tkt_file) <= 4 then tkt_file else @tkt_file end,
	tkt_filename = isnull(nullif(@tkt_filename,''), tkt_filename),
	tkt_mime = isnull(nullif(@tkt_mime,'application/octet-stream'), tkt_mime),
	tkt_size = isnull(nullif(@tkt_size,0), tkt_size),


	tkt_cmpl_dt = @today		-- save the previously calculated value of @today 



WHERE
	(tkt_id = @tkt_id)
                                            

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 38713371
@Rouchie,

should @today be @tkt_cmpl_dt instead? How are you adding the DateTime to field tkt_cmpl_dt if the sts_id value is 72?
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 38713402
Look at the line before WHERE

That is where the value placed in the @today variable is saved into the tkt_cmpl._id column
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now