Click here to Skip to main content
15,894,630 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I've read about a million examples of how to fix the problem but nothing I try seems to be working. I'm trying to insert documents to SQL Server 2005 with C# for a windows app.

As many other there is not problem with the file if it doesn't have the .*x ending. But if you have the .docx etc you get a file corrupt message

I understand from reading everywhere the when you load a .docx or item of this type it adds and extra byte to what is being written into the database.

Here is my code for the insert:
C#
openFileDialog1.Title = "Upload Files";
             openFileDialog1.Filter = "PDF File (*.pdf)|*.pdf|All Files (*.*)|*.*";
             // openFileDialog1.Filter = "PDF File (*.pdf)|*.pdf|DOC File (*.doc*)|*.doc*|TXT File (*.txt)|*.txt|Excel Files (*.xls*)|*.xls*";
             openFileDialog1.FilterIndex = 1; // set on Word Files (*.doc)
             openFileDialog1.FileName = " ";
             openFileDialog1.ShowDialog();
             openFileDialog1.CheckFileExists = true;
             TBPath.Text = openFileDialog1.FileName;

             string pathold = TBPath.Text;
             string filename = Path.GetFileName(pathold);
             string strDocExt = Path.GetExtension(pathold);



             string query = "SELECT * From P2_Files where Control_No = '" + ControlNo.Trim() + "' and  DocTitle = '" + filename + "'";
             SqlDataAdapter da = new SqlDataAdapter(query, clsDataSource.conn);
             DataSet ds = new DataSet();
             da.Fill(ds);

             FileStream fs = new FileStream(pathold, FileMode.Open);
             Byte[] Doc = new Byte[fs.Length];
             fs.Read(Doc, 0, (int)fs.Length);
             fs.Close();
             query = "INSERT into P2_Files (Control_No,Doc,DocTitle,DocType) Values ('" + ControlNo + "', @Doc,'" + filename + "','" + strDocExt + "') ";

             clsDataSource.OpenConnection(clsDataSource.conn);
             SqlCommand cmd = new SqlCommand(query, clsDataSource.conn);

             SqlParameter doc = new SqlParameter("@Doc", SqlDbType.VarBinary);
             doc.Value = Doc;
             cmd.Parameters.Add(doc);
             try
             {
                 cmd.ExecuteNonQuery();
                 MessageBox.Show("File Uploaded executed.");
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message, "Save Image to DB");
             }
             ViewFiles();
         }




Here is the code for retrieving:

string query = "SELECT DocType From P2_Files where Control_No = '" + ControlNo.Trim() + "' and  DocTitle = '" + filename + "'";
                   SqlDataAdapter da = new SqlDataAdapter(query, clsDataSource.conn);

                   DataSet ds = new DataSet();
                   da.Fill(ds);

                   string strExtenstion = ds.Tables[0].Rows[0]["DocType"].ToString();


                   string sql = "SELECT doc FROM P2_Files WHERE Control_No = '" + ControlNo.Trim() + "' and  DocTitle = '" + filename + "'";

                   SqlCommand cmd = new SqlCommand(sql, clsDataSource.conn);
                   cmd.CommandType = CommandType.Text;


                   object doc = cmd.ExecuteScalar();



                   string tempDir = Path.GetTempPath();

                   LaunchedFile = tempDir + filename;
                   FileInfo fi = new FileInfo(LaunchedFile);
                   FileStream fs = fi.Create();
                   byte[] Data = new byte[0];
                   Data = ((byte[])doc);
                   int ArraySize = new int();
                   ArraySize = Data.GetUpperBound(0)-1;
                   fs.Write(Data, 0, ArraySize);
                   fs.Flush();
                   fs.Close();



                   Launcher = new Process();
                   Launcher.StartInfo.FileName = fi.FullName;
                   Launcher.StartInfo.WorkingDirectory = tempDir;
                   Launcher.Start();


Please Help! :-)
Posted
Comments
lewax00 12-Nov-12 11:27am    
"As many other there is not problem with the file if it doesn't have the .*x ending. But if you have the .docx etc you get a file corrupt message"

Are you saying that you're changing a file from (for example) "abc.doc" to "abc.docx" and it doesn't open?
Angela Scheuvront 12-Nov-12 11:34am    
No If I try to retrieve a .docx or .pptx or .xlsx file from the database is when I get the corrupt error.

Other files open fine
lewax00 12-Nov-12 11:56am    
Not sure why it would work with one but not the other, but you can simplify some of this using the System.IO.File class (specifically, ReadAllBytes and WriteAllBytes), the documentation is here: http://msdn.microsoft.com/en-us/library/system.io.file.aspx

Specifically:
FileStream fs = new FileStream(pathold, FileMode.Open);
Byte[] Doc = new Byte[fs.Length];
fs.Read(Doc, 0, (int)fs.Length);
fs.Close();

Can be shortened to:
byte[] Doc = File.ReadAllBytes(pathold);

And:
FileInfo fi = new FileInfo(LaunchedFile);
FileStream fs = fi.Create();
byte[] Data = new byte[0];
Data = ((byte[])doc);
int ArraySize = new int();
ArraySize = Data.GetUpperBound(0)-1;
fs.Write(Data, 0, ArraySize);
fs.Flush();
fs.Close();

Can be:
byte[] Data = new byte[0];
Data = ((byte[])doc);
File.WriteAllBytes(LaunchedFile, Data);

Less steps makes it easier to diagnose.
Angela Scheuvront 12-Nov-12 12:13pm    
thanks for that tip.

I has something to do with 2007 documents really being a type of zip file and not the actual document. An when you upload those versions it adds an extra byte on the end of the file size.

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