Click here to Skip to main content
15,887,683 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am working on a help-desk ticketing system. I'm having a problem saving the value of the ticketID as a session variable because it's an identity column in the Helpdesk table. 
Im saving tickets to the helpdesk table fine but i need to save the value of the ticketID because the File table references the Helpdesk table's TickeID as a one to many relationship,
so that one ticket can have many files attached to them. I am trying to use the "Upload and Download Files with SQL Server in ASP.NET" from CodeProject as an example except it doesn't
show me how to save the session variable.
 
My code for the Utilities/FIleUtilites.cs is as follows




C#
public class FileUtilities
    {
        private static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["status"].ConnectionString;
        }
 
        private static void OpenConnection(SqlConnection connection)
        {
            connection.ConnectionString = GetConnectionString();
            connection.Open();
        }
 
        // Get the list of the files in the database
        public static DataTable GetFileList(int TicketID)
        {
            var fileList = new DataTable();
            using (var connection = new SqlConnection())
            {
                OpenConnection(connection);
                var cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;
 
                cmd.CommandText = "SELECT FileID, TicketID, ContentType, Size, Data FROM Files WHERE TicketID=@TicketID";
                cmd.CommandType = CommandType.Text;
                var adapter = new SqlDataAdapter();
                cmd.Parameters.Add("@TicketID", TicketID);
                adapter.SelectCommand = cmd;
                adapter.Fill(fileList);
 
                connection.Close();
            }
 
            return fileList;
        }
        // Save a file into the database
        public static void SaveFile(string name, int ticketID, string contentType,
           int size, byte[] data)
        {
            using (var connection = new SqlConnection())
            {
                // Due to the limit of the max for a int type, the largest file can be
                // uploaded is 2147483647, which is very large anyway.

                OpenConnection(connection);
                var cmd = new SqlCommand { Connection = connection, CommandTimeout = 0 };
 
                const string commandText = "INSERT INTO Files(FileID, TicketID, ContentType, Size, Data, Name) " +
                                           "VALUES( @TicketID, @ContentType, @Size, @Data, @Name)";
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
 

                cmd.Parameters.Add("@TicketID", SqlDbType.Int);
                cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Size", SqlDbType.Int);
                cmd.Parameters.Add("@Data", SqlDbType.VarBinary);
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
 
                cmd.Parameters["@TicketID"].Value = ticketID;
                cmd.Parameters["@ContentType"].Value = contentType;
                cmd.Parameters["@size"].Value = size;
                cmd.Parameters["@Data"].Value = data;
                cmd.Parameters["@Name"].Value = name;
                cmd.ExecuteNonQuery();
 
                connection.Close();
            }
        }
 

        // Get a file from the database by ID
        public static DataTable GetAFile(int FileID, int TicketID)
        {
            var file = new DataTable();
            using (var connection = new SqlConnection())
            {
                connection.Open();
                var cmd = new SqlCommand
                {
                    Connection = connection,
                    CommandTimeout = 0,
                    CommandText = "SELECT Files.FileID, Files.TicketID, Files.ContentType, Files.Size, Files.Data, Files.Name, HelpDesk.TicketID FROM Files " +
                                  "INNER JOIN HelpDesk"+
                                  "ON Files.TicketID= HelpDesk.TicketID"+
                                  " WHERE HelpDesk.TicketID =@TicketID and Files.FileID=@FileID",
                    CommandType = CommandType.Text
                };
 
					var adapter = new SqlDataAdapter();
	 
					cmd.Parameters.Add("@TicketID", SqlDbType.Int);
					cmd.Parameters.Add("@FileID", SqlDbType.Int);
					cmd.Parameters["@TicketID"].Value = TicketID;
					cmd.Parameters["@FileID"].Value =FileID;
					adapter.SelectCommand = cmd;
					adapter.Fill(file);
					connection.Close();
				}
			return file;
			}
		}
	}


now i need to save  a ticket first so that i can reference the TicketID  as a session variable so that i can refference that session variable in my SaveFile method from the fileUtilities class.
 
the code for the BtnSave_Click Event for the NewTicket.aspx.cs is as follows




C#
protected void btnSave_Click(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                btnUpload.Visible = true;
                lblUpload.Visible = true;
                FileUpload1.Visible = true;
                try
                {
                    int FromUser = ddlFromUser.SelectedIndex + 1;
                    int AssignedTo = ddlAssignTo.SelectedIndex + 1;
                    int Category = ddlCategory.SelectedIndex + 1;
                    string subject = txtSubject.Text;
                    string description = txtBody.Text;
                    string DateAdded = DateTime.Now.ToString(@"yyyy-MM-dd hh:mm tt");
                    string dt = txtDatePicker.Text;
                    DateTime dtDueDate = Convert.ToDateTime(dt);
                    string DueDate = dtDueDate.ToString(@"yyyy-MM-dd");
                    string priority = rblPriority.SelectedItem.Text;
                    

                    // Define data objects
                    SqlConnection conn;
                    SqlCommand comm;
                    // Read the connection string from Web.config
                    var constring = ConfigurationManager.ConnectionStrings["status"].ConnectionString;
                    // Initialize connection
                    conn = new SqlConnection(constring);
                    //create a query to insert the record into the table
                    string query =
                        "INSERT INTO HelpDesk(EmployeeIDFrom, EmployeeIDAssignedTo, CategoryID, Subject, Description, DateAdded, DueDate, Status, Priority)" +
                        " VALUES('" + FromUser + "','" + AssignedTo + "','" + Category + "','" + subject + "','" +
                        description + "','" + DateAdded + "','" + DueDate + "','" + "Open" + "','" + priority + "')"
                        ;
                    
                    SqlCommand cmd = new SqlCommand(query, conn);
                    // Open the connection
                    conn.Open();
                    //Call the executeNonQuery method to save record in the table
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "Select @@Identity";
                    var TicketID = cmd.ExecuteScalar();//This is where i need to save the session variable
                    Session["NewTicketID"] = TicketID;
                    
                    //Close the connection
                    conn.Close();
                    
                    sendMail();
                 

                }
                catch (Exception ex)
                {

                    dbErrorMessage.Text = ex.Message;
                }
                
            }

        }

Now I just need to know how to save the session variable so that i can refference it in my btnUpload_Click event
which goes like this:




C#
protected void btnUpload_Click(object sender, EventArgs e)
        {
            // Although I put only one http file control on the aspx page,
                    // the following code can handle multiple file controls in a single aspx page.
                    HttpFileCollection files = Request.Files;
                    foreach (string fileTagName in files)
                    {
                        HttpPostedFile file = Request.Files[fileTagName];
                        if (file.ContentLength > 0)
                        {
                            // Due to the limit of the max for a int type, the largest file can be
                            // uploaded is 2147483647, which is very large anyway.
                            int size = file.ContentLength;
                            string name = file.FileName;
                            int position = name.LastIndexOf("\\");
                            name = name.Substring(position + 1);
                            string contentType = file.ContentType;
                            byte[] fileData = new byte[size];
                            int ticketID = int.Parse()
                            
                            file.InputStream.Read(fileData, 0, size);
 
                            FileUtilities.SaveFile(name, ticketID, contentType, size, fileData );
                        }
                    }
        }


Pleasen help me fix this horrible head ache
Posted
Updated 6-Jul-14 23:01pm
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900