want to upload and download word document into SQL

[Terry]

Hello:

Using vb.net 3.5

I have a sql table with a binary field (sql 2000)

This is for a word document.

to insert into sql, I use
Dim fs As New System.IO.FileStream(FileNameandPath, IO.FileMode.Open,
IO.FileAccess.Read)
Dim b(fs.Length() - 1) As Byte
fs.Read(b, 0, b.Length)
fs.Close()

call stored procedure

Dim q As New SqlParameter("@BLOB", SqlDbType.Binary, b.Length,
ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, b)

-> I can see that something is inserted into the table.

to get the document, I call another stored procedure

Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim buffer As Byte() =
System.Text.Encoding.Default.GetBytes(cmd.ExecuteScalar())
Dim fs As New FileStream(DesktopPath + "/" + Var_NewFileName,
FileMode.Create)
fs.Write(buffer, 0, buffer.Length)
fs.Close()

When I do this, a file is created but it is either empty or I get prompted
for the encoding...

Any suggestions ?

Thanks

 

[Armin Zingler]

System.Text.Encoding.Default.GetBytes(cmd.ExecuteScalar())

Which data type does cmd.ExecuteScalar return? Assign it to an Object
variable for this purpose and look at the actual type.
System.Text.Encoding is not the right way to work with the data because
it's binary data, not text.


Armin

 

[Joe Cool]

Hello:

Using vb.net 3.5

I have a sql table with a binary field (sql 2000)

This is for a word document.

to insert into sql, I use
Dim fs As New System.IO.FileStream(FileNameandPath, IO.FileMode.Open,
IO.FileAccess.Read)
Dim b(fs.Length() - 1) As Byte
fs.Read(b, 0, b.Length)
fs.Close()

call stored procedure

Dim q As New SqlParameter("@BLOB", SqlDbType.Binary, b.Length,
ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, b)

-> I can see that something is inserted into the table.

to get the document, I call another stored procedure

Dim dr As SqlDataReader = cmd.ExecuteReader()
Dim buffer As Byte() =
System.Text.Encoding.Default.GetBytes(cmd.ExecuteScalar())
Dim fs As New FileStream(DesktopPath + "/" + Var_NewFileName,
FileMode.Create)
fs.Write(buffer, 0, buffer.Length)
fs.Close()

When I do this, a file is created but it is either empty or I get prompted
for the encoding...

Any suggestions ?

I used toi do that with JPGs. I have switched to SQL2005 and use the
new varbinary(MAX) datatype, but with SQL2000 I used an Image
datetype. I would cast the return value of the ExecuteScalar to a byte
array and assign it to a byte array. Then load the byte array in an
Image variable with an Image.FromStream using a Memory Stream and the
byte array.