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.FilterIndex = 1;
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! :-)