trouble parsing Excel file to byte array

[Andre Ranieri]

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(sFil ePath, 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 .ToShortDateStr ing()
SqlConnection cn = new SqlConnection(M enu.sConnect)
SqlCommand cmd = cn.CreateComman d()
cmd = cn.CreateComman d()
cn.Open()
cmd.CommandText = sSQL
cmd.Parameters. Add("@AccountKe y", SqlDbType.Int, 4)
cmd.Parameters["@AccountKe y"].Value = myReportDS.Acco unt[0].AccountKey
cmd.Parameters. Add("@Descripti on", SqlDbType.VarCh ar, 50).Value = sDescription
cmd.Parameters. Add("@ObjectTyp e", SqlDbType.Image , file.Length).Va lue = file
string sSQL = "INSERT INTO tblSupportAccou ntsOLEObjects (AccountKey, Description, ObjectType) "
"VALUES (@AccountKey, @Description, @ObjectType);"
cmd.CommandText = sSQL
cmd.CommandTime out = 0
cmd.ExecuteNonQ uery()
cn.Close();

catch (Exception ex

MessageBox.Show ("Exception at Print900.AddFil e(string sAccountKey)\r\ n" + ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has occured.", MessageBoxButto ns.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(byte s, 0, bytes.Length);
input.Close();

FileStream output = new FileStream("C:\ \test2.xls", FileMode.Create ,
FileAccess.Writ e);
output.Write(by tes, 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.
"Andre Ranieri" <an*******@disc ussions.microso ft.com> wrote in message
news:E6******** *************** ***********@mic rosoft.com...

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(sFil ePath, 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 .ToShortDateStr ing(); SqlConnection cn = new SqlConnection(M enu.sConnect);
SqlCommand cmd = cn.CreateComman d();
cmd = cn.CreateComman d();
cn.Open();
cmd.CommandText = sSQL;
cmd.Parameters. Add("@AccountKe y", SqlDbType.Int, 4);
cmd.Parameters["@AccountKe y"].Value = myReportDS.Acco unt[0].AccountKey;
cmd.Parameters. Add("@Descripti on", SqlDbType.VarCh ar, 50).Value = sDescription; cmd.Parameters. Add("@ObjectTyp e", SqlDbType.Image , file.Length).Va lue = file; string sSQL = "INSERT INTO tblSupportAccou ntsOLEObjects (AccountKey, Description, ObjectType) " + "VALUES (@AccountKey, @Description, @ObjectType);";
cmd.CommandText = sSQL;
cmd.CommandTime out = 0;
cmd.ExecuteNonQ uery();
cn.Close();
}
catch (Exception ex)
{
MessageBox.Show ("Exception at Print900.AddFil e(string sAccountKey)\r\ n" + ex.Source + "\r\n" + ex.Message + "\r\n" + ex.StackTrace, "An error has
occured.", MessageBoxButto ns.OK, MessageBoxIcon. Error); return;
}
}