locked
If Fileupload is blank do not update Database Varbinary RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    I have a fileUpload function which is used to update database records following the previous upload. Within the fileUpload the document uploaded is saved into the database as Data (Varbinary(MAX)) as well as the ContentType (varchar(50)) and Extension (varchar(8)). On the fileUpload the document may not always be replaced just some of the data that is also inserted into the table (e.g dates, reference number, Name etc.) during the upload I would like to check if the fileUpload is blank and if so not to updated the Data, ContentType and Extension columns within the database as the document has not been updated. I have added my code below but am unsure how I can do this.

    C# Code:
    
    protected void btnUpdate_Click(object sender, EventArgs e)
            {
                using (SqlCommand cmd = new SqlCommand("UpdateDoc", con))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        string DocName = Path.GetFileName(FileUpload2.PostedFile.FileName);
                        string contentType = FileUpload2.PostedFile.ContentType;
                        string extension = Path.GetExtension(DocName);
                        using (Stream fs = FileUpload2.PostedFile.InputStream)
                        {
                            using (BinaryReader br = new BinaryReader(fs))
                            {
                                byte[] bytes = br.ReadBytes((Int32)fs.Length);
    
                                id = IDtxt.Text;
    
                                System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
                                dateInfo.ShortDatePattern = "{0:dd/MM/yyyy}";
                                DateTime date = Convert.ToDateTime(Datetxt.Text, dateInfo);
    
    
                                cmd.CommandType = CommandType.StoredProcedure;
                                //SqlCommand cmd = new SqlCommand("UPDATE Portal_HS_PolicyDocs SET DocRefNo=@DocRefNo, Date=@Date, ContentType=@ContentType, Data=@Data, Extension=@Extension WHERE ID=@Id");
                                cmd.Parameters.AddWithValue("@Id", id);
                                cmd.Parameters.AddWithValue("@RefNo", SqlDbType.Text).Value = RefNotxt.Text;
                                cmd.Parameters.AddWithValue("@Date", SqlDbType.DateTime).Value = date;
     = uploadedbyID;
                                cmd.Parameters.AddWithValue("@ContentType", contentType);
                                cmd.Parameters.AddWithValue("@Data", bytes);
                                cmd.Parameters.AddWithValue("@Extension", extension);
    
                                con.Open();
                                cmd.ExecuteNonQuery();
                                con.Close();
                            }
                        }
    
    
    Stored Procedure Code:
    
    ALTER PROCEDURE UpdateDoc
    	@ID int,
    	@RefNo varchar(50),
    	@Date datetime,
    	@ContentType varchar(50),
    	@Extension varchar(8),
    	@Data varbinary(MAX)
    	
    	AS
    
    	Update [dbo].[Table]
    	SET [RefNo] = @RefNo
    	,[Date] = @Date
    	,[ContentType] = @ContentType
    	,[Extension] = @Extension
    	,[Data] = @Data
    WHERE ID = @ID
    

    Any help would be greatly appreciated.

    Jonny

    Wednesday, April 24, 2019 3:20 PM

Answers

  • User753101303 posted

    The source issue is that it happens because you were updating blindly all fields even if no file at all is uploaded (and so it is exposed as a zero length file)

    As explained earlier you could change the current SP to test which information you really have or have two SPs so that you can update only the columns you want based on the which information you got. Have you tried past suggestions ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 25, 2019 10:26 AM
  • User475983607 posted

    jonnygareth30

    Again thank you for your response, I have tried as you have mentioned previously by splitting the SP and using the code you have provided above but the same issue occurs.

    It does not make sense why you cannot code this. It seems like you are jamming everything down one stored procedure. 

    How about creating a second stored procedure that updates only the expiration date or fields you want.  Then in the if condition, call one or the other proc. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 25, 2019 11:05 AM

All replies

  • User475983607 posted

    during the upload I would like to check if the fileUpload is blank and if so not to updated the Data, ContentType and Extension columns within the database as the document has not been updated.

    Pretty simple.

    if (FileUpload1.HasFile)
    {
       //Do file stuff
    }
    else
    {
      //No upload file found
    }

    Reference documentaiton

    https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.fileupload?view=netframework-4.8

    Wednesday, April 24, 2019 3:31 PM
  • User-1901014284 posted

    Thank you mgebhard,

    but the else section is where i am struggling with, for example if

    if (FileUpload1.HasFile)
    {
       cmd.Parameters.AddWithValue("@ContentType", contentType);
       cmd.Parameters.AddWithValue("@Data", bytes);
       cmd.Parameters.AddWithValue("@Extension", extension);
    }
    
    

    Then allow the File to be uploaded as well as the parameters. If a file has not been uploaded then I would like all data to be committed to the database excluding the 3 parameters mentioned above and I am not sure how to do this under the 

    else
    {
       //No upload file found
    }

    section of the code.

    Many thanks.

    Jonny

    Wednesday, April 24, 2019 3:49 PM
  • User475983607 posted

    Then allow the File to be uploaded as well as the parameters.

    I think there is a misunderstanding.  At this point, the user has already decided to upload a file or not.   The best you can do is check if the file exists.

    Perhaps you need to rethink the approach?

    Wednesday, April 24, 2019 4:07 PM
  • User753101303 posted

    Hi,

    You could pass DBNull.Value explicitely or use @Data varbinary(max)=NULL so that it is NULL by default. Your SP could then test that to update only the needed columns (likely using two separate UPDATE statements).

    Another option could be to use explicitely two separate SP, each one updating the relevant columns...

    Not sure which approach you prefer ?

    ie it could be something such as :

    ALTER PROCEDURE UpdateDoc
    	@ID int,
    	@RefNo varchar(50),
    	@Date datetime,
    	@ContentType varchar(50)=NULL,
    	@Extension varchar(8)=NULL,
    	@Data varbinary(MAX)=NULL
    	AS
    	Update [dbo].[Table]
    	SET [RefNo] = @RefNo
    	,[Date] = @Date
            WHERE ID = @ID
    
            IF @ContentType IS NOT NULL OR @Extension IS NOT NULL OR @Data IS NOT NULL
    	   Update [dbo].[Table]
    	   SET [ContentType] = @ContentType
    	   ,[Extension] = @Extension
    	   ,[Data] = @Data
               WHERE ID = @ID -- And more likely fails anyway if not all 3 are not NULL ?
    

    Wednesday, April 24, 2019 4:16 PM
  • User-1901014284 posted

    Hi both,

    Thank you for your responses, the problem I have is the user may need to only change the Expiry Date of a document which stored in the DB under the date column which is why the document would not need to be re-uploaded into the system after the Expiry Date has been updated. At the moment when the Expiry Date is updated and the FileUpload is left blank the current VarBinary(MAX) is replaced with a blank document labelled the same name as my DB table with the FileType just as File. 

    Again many thanks for your help, it is greatly appreciated.

    Jonny

    Thursday, April 25, 2019 9:53 AM
  • User753101303 posted

    The source issue is that it happens because you were updating blindly all fields even if no file at all is uploaded (and so it is exposed as a zero length file)

    As explained earlier you could change the current SP to test which information you really have or have two SPs so that you can update only the columns you want based on the which information you got. Have you tried past suggestions ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 25, 2019 10:26 AM
  • User-1901014284 posted

    Hi PatriceSc,

    Again thank you for your response, I have tried as you have mentioned previously by splitting the SP and using the code you have provided above but the same issue occurs.

    Many thanks

    Jonny

    Thursday, April 25, 2019 11:01 AM
  • User475983607 posted

    jonnygareth30

    Again thank you for your response, I have tried as you have mentioned previously by splitting the SP and using the code you have provided above but the same issue occurs.

    It does not make sense why you cannot code this. It seems like you are jamming everything down one stored procedure. 

    How about creating a second stored procedure that updates only the expiration date or fields you want.  Then in the if condition, call one or the other proc. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 25, 2019 11:05 AM
  • User-1901014284 posted

    Thank you both for your responses,

    I now have this working, my mistake was I had not removed the DocName from the second SP so this was still being updated in both SP's causing the issue.

    Again thank you very much for all your help it is greatly appreciated.

    Jonny

    Thursday, April 25, 2019 1:36 PM