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();
}
}
}
}
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
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
Red Line Error Message:Add "Value" to that line:
Operator '!=' cannot be applied to operands of type 'System.Web.UI.WebControls.ListItem' and 'string'
if (theDdlInQuestion.SelectedItem.Value != "Closed")
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)
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();
}
}
}
}
Declare d datetime
SET d = GetDate()
UPDATE myTable SET status = 'closed', completionDate = d WHERE ID = someID