I am trying to read data from a form and update one table while adding media to a different table but the connection closes as soon as I execute the update query. How do I keep it open and run the insert query for the second table?

 try
        {
            dbConn.Open();
            OleDbAdapter = new OleDbDataAdapter(cmd1, dbConn);
            OleDbAdapter.SelectCommand.Parameters.Add("@buttonClicked", OleDbType.Integer).Value = archiveIdNumber.InnerText.Substring(33);
            OleDbAdapter.Fill(infoDs, "First Table");
            OleDbAdapter.SelectCommand.CommandText = cmd2;
            OleDbAdapter.Fill(infoDs, "Second Table");
            OleDbAdapter.Dispose();

            string cmdString = "Update ARCHIVE_DECADE_TBL Set PRODUCT_NAME='" + Request.Form["nameBox"] + "', MODEL_NUMBER='" + Request.Form["modelBox"] + "', YEAR_INTRODUCED='" + Request.Form["startBox"] + "', YEAR_DISCONTINUED='" + Request.Form["endBox"] + "', PRODUCT_LINE='" + Request.Form["lineBox"] + "', LOCATION='" + Request.Form["locationBox"] + "', QUANTITY='" + Request.Form["quantityBox"] + "' " +
                "Where ARCHIVE_ID_NUMBER=" + int.Parse(Request.Form["archiveBox"]);

            OleDbAdapter.UpdateCommand = new OleDbCommand(cmdString, dbConn);

            OleDbAdapter.UpdateCommand.ExecuteNonQuery();

            if (addPhotos.HasFiles)
            {
                //cmdString = "Update ARCHIVE_DECADE_TBL Set PRODUCT_NAME='" + Request.Form["nameBox"] + "', MODEL_NUMBER='" + Request.Form["modelBox"] + "', YEAR_INTRODUCED='" + Request.Form["startBox"] + "', YEAR_DISCONTINUED='" + Request.Form["endBox"] + "', PRODUCT_LINE='" + Request.Form["lineBox"] + "', LOCATION='" + Request.Form["locationBox"] + "' " +
                //"Where ARCHIVE_ID_NUMBER=" + int.Parse(Request.Form["archiveBox"]);
                var x = 0;
                if (!System.IO.Directory.Exists(Server.MapPath("includes/images/archives/" + Request.Form["archiveBox"] + "_1")))
                {
                    System.IO.Directory.CreateDirectory(Server.MapPath("includes/images/archives/" + Request.Form["archiveBox"] + "_1"));
                }

                String filePath = Server.MapPath("includes/images/archives/" + Request.Form["archiveBox"] + "_1");
                HttpFileCollection uploadedFiles = Request.Files;

                for (int i = 0; i < uploadedFiles.Count; i++)
                {
                    HttpPostedFile userPostedFile = uploadedFiles[i];
                    if (userPostedFile.ContentLength > 0)
                    {
                        string extension = Path.GetExtension(userPostedFile.FileName);
                        //Request.Form["archiveBox"] + "_" + i will be the same as the "IMAGE" column in the IMAGE_TBL
                        uploadedFiles[i].SaveAs(filePath + "/" + Request.Form["archiveBox"] + "_" + (i + 1) + extension);

                    }
                    x++;
                }


                cmdString = "INSERT INTO ARCHIVE_IMAGE_TBL (ARCHIVE_ID, MODEL_NUMBER, LOCATION, IMAGE, NUMBER_OF_IMAGES, IMAGE_FILE_TYPE) VALUES ('" + Request.Form["archiveBox"] + "', '" + Request.Form["modelBox"] + "', '" + Request.Form["locationBox"] + "', '" + Request.Form["archiveBox"] + "_1" + "', '" + uploadedFiles.Count + "', '.jpg');";

                OleDbAdapter.InsertCommand = new OleDbCommand(cmdString, dbConn);

                OleDbAdapter.InsertCommand.ExecuteNonQuery();
            }
            dbConn.Close();
        }

Related posts

Recent Viewed