Expert: Srini Nagarajan - 7/22/2011

Question
QUESTION: I am relatively new to ASP.net so forgive me if I don't understand some concepts. Was attempting to use ASP.net and C# to do a file upload to SQL database using the following URL: http://www.dotnettutorials.com/tutorials/database/upload-files-sql-database-cs.a. When attempt to click my upload button nothing gets sent to database. Code is below:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

namespace ChicagoBulls
{
   public partial class _Default : System.Web.UI.Page
   {
       protected void Page_Load(object sender, EventArgs e)
       {

       }

       protected void btn_Upload_Click(object sender, EventArgs e)
       {
         if (FileToUpload.PostedFile == null || String.IsNullOrEmpty(FileToUpload.PostedFile.FileName) || FileToUpload.PostedFile.InputStream == null)
         {
         lit_Status.Text = "<br />Error - unable to upload file. Please try again.<br />";
         }
         else
         {
         using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
         {
         try
         {
         const string SQL = "INSERT INTO ChicagoBulls ([firstname], [initial], [lastname], [studentid], [phone], [email], [textnotes], [MIME], [BinaryData], [DateTimeUploaded]) VALUES (@firstname, @initial, @lastname, @studentid, @phone, @email, @textnotes, @MIME, @BinaryData, @DateTimeUploaded)";
         SqlCommand cmd = new SqlCommand(SQL, Conn);
         cmd.Parameters.AddWithValue("@firstname", FirstName.Text.Trim());
         cmd.Parameters.AddWithValue("@initial", Initial.Text.Trim());
         cmd.Parameters.AddWithValue("@lastname", LastName.Text.Trim());
         cmd.Parameters.AddWithValue("@studentid", StudentId.Text.Trim());
         cmd.Parameters.AddWithValue("@phone", Phone.Text.Trim());
         cmd.Parameters.AddWithValue("@email", Email.Text.Trim());
         cmd.Parameters.AddWithValue("@textnotes", textNotes.Text.Trim());
         cmd.Parameters.AddWithValue("@MIME", FileToUpload.PostedFile.ContentType);

         byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
         FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);
         cmd.Parameters.AddWithValue("@BinaryData", imageBytes);
         cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now);

         Conn.Open();
         cmd.ExecuteNonQuery();
         lit_Status.Text = "<br />File successfully uploaded - thank you.<br />";
         Conn.Close();
         }
         catch (Exception ex)
         {
         throw;
         }
         }
         }
       }
   }
}

ANSWER: Hi,

Thanks for the question.

Why do you want to store the image on to database?  please store the image file in some folder and store the file name in the database

Here is the steps to upload the file to a directory.

http://support.microsoft.com/kb/323246

Please let me know if you have any questions.

Thanks

-Srini

---------- FOLLOW-UP ----------

QUESTION: Everything is working the way I want it to except in the codefile, the form always seems to get caught up in the section that is checking to see what the file extension may be. It doesn't get past that test even if the file type is correct...I am not seeing what the issue is at this time. Please advise...code is below:

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

namespace ChicagoBulls
{
   public partial class _Default : System.Web.UI.Page
   {
       protected void Page_Load(object sender, EventArgs e)
       {
         
       }

       protected void btn_Upload_Click(object sender, EventArgs e)
       {
         if (FileToUpload.PostedFile == null || String.IsNullOrEmpty(FileToUpload.PostedFile.FileName) || FileToUpload.PostedFile.InputStream == null)
         {
         lit_Status.Text = "<center><font color='0000ff'><br />Error - unable to upload file. Please try again.<br /></font></center>";
         }
         else if (FileToUpload.HasFile)
         {
         string extension=System.IO.Path.GetExtension(FileToUpload.FileName).ToUpper();
         if (extension != ".doc" || extension != ".docx" || extension != ".pdf" || extension != ".rtf")
         {
         lit_Status.Text = "<center><font color='0000ff'>Error - Your resume format is not .doc, .docx, .pdf or .rtf. Please <a href='default.aspx'><font color='0000ff'>resubmit the form</font></a> to attempt your upload again.<br /><br /></font></center>";
         }
         else
         {
         using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AccentureEventConnectionString"].ConnectionString))
         {
         try
         {
         const string SQL = "INSERT INTO ChicagoBulls ([firstname], [initial], [lastname], [studentid], [phone], [email], [textnotes], [MIME], [BinaryData], [DateTimeUploaded]) VALUES (@firstname, @initial, @lastname, @studentid, @phone, @email, @textnotes, @MIME, @BinaryData, @DateTimeUploaded)";
         SqlCommand cmd = new SqlCommand(SQL, Conn);
         cmd.Parameters.AddWithValue("@firstname", FirstName.Text.Trim());
         cmd.Parameters.AddWithValue("@initial", Initial.Text.Trim());
         cmd.Parameters.AddWithValue("@lastname", LastName.Text.Trim());
         cmd.Parameters.AddWithValue("@studentid", StudentId.Text.Trim());
         cmd.Parameters.AddWithValue("@phone", Phone.Text.Trim());
         cmd.Parameters.AddWithValue("@email", Email.Text.Trim());
         cmd.Parameters.AddWithValue("@textnotes", textNotes.Text.Trim());
         cmd.Parameters.AddWithValue("@MIME", FileToUpload.PostedFile.ContentType);

         byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
         FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);
         cmd.Parameters.AddWithValue("@BinaryData", imageBytes);
         cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now);

         Conn.Open();
         cmd.ExecuteNonQuery();
         lit_Status.Text = "<center><font color='ff0000'><br />File successfully uploaded - thank you.<br /></font></center>";
         Conn.Close();
         }
         catch (Exception ex)
         {
         throw;
         }
         }
         }
         }
       }
   }
}

ANSWER: Hi,

It's a simple mistake.  In the first list you converted to Upper case and in the if condition you are checking lower case.

C# is a case sensitive.

string extension=System.IO.Path.GetExtension(FileToUpload.FileName).ToUpper()
if (extension != ".doc" || extension != ".docx" || extension != ".pdf" || extension != ".rtf")



Change to

string extension=System.IO.Path.GetExtension(FileToUpload.FileName).ToLower()


---------- FOLLOW-UP ----------

QUESTION: Well...I guess I just don't get why this is not working...made the change you suggested (see code below), but no matter if I upload a .doc or .txt file I still get caught up in the section that returns the message "Error - Your file is not the format .doc, .docx, .pdf or .rtf." I am wondering if the structure of my if-then statements is the problem...please advise:

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

namespace ChicagoBulls
{
   public partial class _Default : System.Web.UI.Page
   {
       protected void Page_Load(object sender, EventArgs e)
       {

       }

       protected void btn_Upload_Click(object sender, EventArgs e)
       {
         if (FileToUpload.PostedFile == null || String.IsNullOrEmpty(FileToUpload.PostedFile.FileName) || FileToUpload.PostedFile.InputStream == null)
         {
         lit_Status.Text = "<center><font color='0000ff'><br />Error - unable to upload file. Please try again.<br /></font></center>";
         }
         else if (FileToUpload.HasFile)
         {
         string extension = System.IO.Path.GetExtension(FileToUpload.FileName).ToLower();
         if (extension != ".doc" || extension != ".docx" || extension != ".pdf" || extension != ".rtf")
         {
         lit_Status.Text = "<center><font color='0000ff'>Error - Your resume format is not .doc, .docx, .pdf or .rtf. Please resubmit the form to attempt your upload again.<br /><br /></font></center>";
         }
         else
         {
         using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AccentureEventConnectionString"].ConnectionString))
         {
         try
         {
         const string SQL = "INSERT INTO ChicagoBulls ([firstname], [initial], [lastname], [studentid], [phone], [email], [textnotes], [MIME], [BinaryData], [DateTimeUploaded]) VALUES (@firstname, @initial, @lastname, @studentid, @phone, @email, @textnotes, @MIME, @BinaryData, @DateTimeUploaded)";
         SqlCommand cmd = new SqlCommand(SQL, Conn);
         cmd.Parameters.AddWithValue("@firstname", FirstName.Text.Trim());
         cmd.Parameters.AddWithValue("@initial", Initial.Text.Trim());
         cmd.Parameters.AddWithValue("@lastname", LastName.Text.Trim());
         cmd.Parameters.AddWithValue("@studentid", StudentId.Text.Trim());
         cmd.Parameters.AddWithValue("@phone", Phone.Text.Trim());
         cmd.Parameters.AddWithValue("@email", Email.Text.Trim());
         cmd.Parameters.AddWithValue("@textnotes", textNotes.Text.Trim());
         cmd.Parameters.AddWithValue("@MIME", FileToUpload.PostedFile.ContentType);

         byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
         FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);
         cmd.Parameters.AddWithValue("@BinaryData", imageBytes);
         cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now);

         Conn.Open();
         cmd.ExecuteNonQuery();
         lit_Status.Text = "<center><font color='ff0000'><br />File successfully uploaded - thank you.<br /></font></center>";
         Conn.Close();
         }
         catch (Exception ex)
         {
         throw;
         }
         }
         }
         }
       }
   }
}

Answer
hi,

i found there another simple one.  

Extension normally comes only with text like "doc" or "rtf".  You mentioned ".doc"  please remove the period (".").

It should work.  Also you can do a debug using visual studio and step thru the code.


Thanks

-Srini