trouble parsing Excel file to byte array

[Guest]

I'm having trouble programatically inserting an Excel file into an Image column in our CRM package's SQL 2000 database. The function appears to work ok, but when I attempt to access the file through the application's front end the file appears to be corrupt. The front-end application has a way of inserting files to the column, however when I analyze this in SQL Profiler the contents of the Byte array appear to be quite different than the one my code is generating. I'm not sure if this is an ADO.NET issue or a problem with the way I'm parsing the XLS file into the Byte array.

I've been banging my head on this one for a couple days now, any help would be much appreciated

Thanks

Andr


public static void AddFile(string sAccountKey

tr

FileStream fs = new FileStream(sFilePath, FileMode.Open, FileAccess.Read)
byte[] file = new byte[fs.Length]
fs.Read(file, 0, file.Length)
fs.Close();
string sDescription = "900 Special Billing Invoice " + Menu.datEndDate.ToShortDateString()
SqlConnection cn = new SqlConnection(Menu.sConnect)
SqlCommand cmd = cn.CreateCommand()
cmd = cn.CreateCommand()
cn.Open()
cmd.CommandText = sSQL
cmd.Parameters.Add("@AccountKey", SqlDbType.Int, 4)
cmd.Parameters["@AccountKey"].Value = myReportDS.Account[0].AccountKey
cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = sDescription
cmd.Parameters.Add("@ObjectType", SqlDbType.Image, file.Length).Value = file
string sSQL = "INSERT INTO tblSupportAccountsOLEObjects (AccountKey, Description, ObjectType) "
"VALUES (@AccountKey, @Description, @ObjectType);"
cmd.CommandText = sSQL
cmd.CommandTimeout = 0
cmd.ExecuteNonQuery()
cn.Close();

catch (Exception ex

MessageBox.Show("Exception at Print900.AddFile(string sAccountKey)\r\n" + ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has occured.", MessageBoxButtons.OK, MessageBoxIcon.Error)
return

}

 

[Andy Gaskell]

I tried out this code

FileStream input = new FileStream("C:\\test.xls", FileMode.Open,
FileAccess.Read);
byte[] bytes = new byte[input.Length];
input.Read(bytes, 0, bytes.Length);
input.Close();

FileStream output = new FileStream("C:\\test2.xls", FileMode.Create,
FileAccess.Write);
output.Write(bytes, 0, bytes.Length);
output.Close();

and test2.xls opened just fine in Excel so I think your byte array is
alright. Maybe you should try reading the bytes out of SQL Server and write
them to disk and see if it opens in Excel.

I'm having trouble programatically inserting an Excel file into an Image

column in our CRM package's SQL 2000 database. The function appears to work
ok, but when I attempt to access the file through the application's front
end the file appears to be corrupt. The front-end application has a way of
inserting files to the column, however when I analyze this in SQL Profiler
the contents of the Byte array appear to be quite different than the one my
code is generating. I'm not sure if this is an ADO.NET issue or a problem
with the way I'm parsing the XLS file into the Byte array.

I've been banging my head on this one for a couple days now, any help would be much appreciated.

Thanks,

Andre



public static void AddFile(string sAccountKey)
{
try
{
FileStream fs = new FileStream(sFilePath, FileMode.Open, FileAccess.Read);
byte[] file = new byte[fs.Length];
fs.Read(file, 0, file.Length);
fs.Close();
string sDescription = "900 Special Billing Invoice " + Menu.datEndDate.ToShortDateString();
SqlConnection cn = new SqlConnection(Menu.sConnect);
SqlCommand cmd = cn.CreateCommand();
cmd = cn.CreateCommand();
cn.Open();
cmd.CommandText = sSQL;
cmd.Parameters.Add("@AccountKey", SqlDbType.Int, 4);
cmd.Parameters["@AccountKey"].Value = myReportDS.Account[0].AccountKey;
cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = sDescription;
cmd.Parameters.Add("@ObjectType", SqlDbType.Image, file.Length).Value = file;
string sSQL = "INSERT INTO tblSupportAccountsOLEObjects (AccountKey, Description, ObjectType) " +
"VALUES (@AccountKey, @Description, @ObjectType);";
cmd.CommandText = sSQL;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Exception at Print900.AddFile(string sAccountKey)\r\n" +

ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has
occured.", MessageBoxButtons.OK, MessageBoxIcon.Error);