Whirlpool

I am having trouble trying to save a file to a SQL Server image field using VB.NET 2005. I have tried the following 2 methods from Microsoft:

support.microsoft.com/kb/316887/EN-US

support.microsoft.com/kb/308042

When I step through the code, I don't get any errors, but nothing is saved to the database (the image field remains NULL).

Any help would be appreciated.

In MySQL you create a BLOB field, and you simply insert the binary data.

SQL Server shouldn't be too much different.

Set the column type in SQL Server to varbinary(MAX).

string commandText = "INSERT INTO [x] (FileData) VALUES (@FileData)";

using(SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection)) {
byte[] imageData = new byte[htmlInputFile.PostedFile.Inpu­ tStream.Length];
htmlInputFile.PostedFile.InputStre­ am.Read(imageData, 0, imageData.Length);
sqlCommand.Parameters.AddWithValue­ ("@FileData", imageData);
// execute the command
}

The Elite Geek writes...

and you simply insert the binary data.

Thanks ... I'm just having trouble with the "converting the file into binary data" bit.

alexjcampbell writes...

using(SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection)) {
byte[] imageData = new byte[htmlInputFile.PostedFile.Inpu­ tStream.Length];
htmlInputFile.PostedFile.InputStre­ am.Read(imageData, 0, imageData.Length);
sqlCommand.Parameters.AddWithValue­ ("@FileData", imageData);

Thanks, I was really after some ADO.NET code as I am doing it from a Windows Form, not an ASP.NET page. However I'll try changing the field type to VarBinary(MAX) and see if this works with the Microsoft code.

jaffa jnr writes...

I was really after some ADO.NET code as I am doing it from a Windows Form, not an ASP.NET page

ADO.Net is used by both WinForms and ASP.Net. The method of inserting the file into the database is exactly the same, it's just how you retrieve the file that is different.

Just read the file into a byte[] using a stream, and then pass this into the sqlCommand.AddWithValue(string, object) method.