Updating a file to Sql server database

[pinkpanther27]

Hello,

I'm trying to figure out how to save a file (.pfs) to a sql server database table, I've had plenty of experience saving text information but never a file.

Could someone please give me an example of how to do this, or a reference to an example of how to do this.

Thanks

Pink

 

[vbkev]

Private Sub StoreFile(ByVal FileName As String)

Dim oArray() As Byte

Dim oStream As System.IO.FileStream

Try

'Open the specified file and read it into a byte array

oStream = System.IO.File.Open(FileName, IO.FileMode.Open)

ReDim oArray(oStream.Length - 1)

oStream.Read(oArray, 0, oStream.Length)

oStream.Close()

'oArray now stores the file as a byte array

'put your code here to Insert it into an Image field in the database

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub RetrieveFile(ByVal FileName As String)

Dim oArray() As Byte

Dim oStream As System.IO.FileStream

Try

'Put your code here to Retrieve data from database and store in oArray

'Data returned from an Image field is an array of type Byte

'Then write it to a file like this

oStream = System.IO.File.Open(FileName, IO.FileMode.OpenOrCreate)

oStream.Write(oArray, 0, oArray.Length)

oStream.Close()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

 

[pinkpanther27]

I am working with a pdf file, and not an image. and after downloading the pdf file it does not open, I'm trying to figure out what I have down wrong here is my code for down loading it:


Try

Dim con As New SqlConnection("Initial catalog......")

Dim da As New SqlDataAdapter _

("Select * From MyImages", con)

Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)

Dim ds As New DataSet()

con.Open()

da.Fill(ds, "MyImages")

Dim myRow As DataRow

myRow = ds.Tables("MyImages").Rows(0)

Dim MyData() As Byte

MyData = CType(myRow("imgField"), Byte())

Dim K As Long

K = UBound(MyData)

Dim fs As New FileStream _

("C:\copy of Employee Extension List.pdf", FileMode.OpenOrCreate, _

FileAccess.Write)

fs.Write(MyData, 0, CType(K, Integer))

fs.Close()

fs = Nothing

MyCB = Nothing

ds = Nothing

da = Nothing

con.Close()

con = Nothing

MsgBox("Image retrieved")

Catch ex As Exception

MsgBox(ex.Message.ToString)

End Try

 

[EdAlaska]

Store the location of the file instead.

It would seem to me that it would be a lot easier if you simply stored the location in your active directory as to where this PDF file can be located rather than make a duplicate copy of the file in a sql server table/field. Certainly take up a lot less space and process a lot faster. Have you given this any thought/merit? I do this with image files. I have found that its a lot faster simply storing the file name location in a text field in my DB table. A lot less processing.

Ed.