Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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:
   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! Smile | :)
Posted 12-Nov-12 5:20am
Comments
lewax00 at 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 at 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 at 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 at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 662
1 ChauhanAjay 170
2 CHill60 130
3 Maciej Los 125
4 CPallini 110
0 Sergey Alexandrovich Kryukov 9,735
1 OriginalGriff 6,989
2 CPallini 5,825
3 George Jonsson 4,017
4 Gihan Liyanage 3,266


Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 12 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100