Accessing documents from SQL Server database with VB.NET

[OrlaL]

Hi,

I have saved documents into my SQL Server 2005 database and they have been converted to bytes. My web page is made with VB.NET in Visual Studio 2008 with ASP.NET. The following is exactly what I need to achieve;

1. Allow user select document with a button


2. Select the document from the SQL Server table

3. Allow the user to download this document.

The error I am getting is that Category is set as Byte and it can't be passed through GetDocument because filePath is a String. I think that I am pulling the file path from the database wrong, any help would be greatly appreciated!

[
Dim Category As Byte() = Nothing
Dim fs As FileStream
Dim br As BinaryReader
Dim document As Byte()
Dim documentFilePath As String

Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click

myConnection = New SqlConnection("server=ORLA-PC\SQLEXPRESS;database=Edpac;Trusted_Connection=Yes")
myConnection.Open()

myCommand = New SqlCommand("Select FileURL from tblSubmittalFiles where FileID = 1", myConnection)

Dim dr21 As SqlDataReader = myCommand.ExecuteReader()
If dr21.HasRows Then
Do While dr21.Read()
For Me.Columns = 0 To dr21.FieldCount - 1
Category = dr21.Item(Columns)
Next
documentFilePath = Category
GetDocument(documentFilePath)
Loop

End If
dr21.Close()

myConnection.Close()

End Sub


Public Sub GetDocument(ByVal filePath As String)

fs = New FileStream(filePath, FileMode.Open, FileAccess.Read)
br = New BinaryReader(fs)

document = br.ReadBytes(Int(fs.Length))
MsgBox(document.ToString)

br.Close()
fs.Close()
End Sub
]

 

[pmegan]

What is this doing? It looks like you're always passing dr21.Item(dr21.FieldCount - 1) into GetDocument().

Do While dr21.Read()
  For Me.Columns = 0 To dr21.FieldCount - 1
    Category = dr21.Item(Columns)
  Next
  documentFilePath = Category
  GetDocument(documentFilePath)
Loop

Also, when you step through, which line is throwing the exception?

 

[OrlaL]

Unfortunatly im new to Do While Loops and unsure about how to get what I want from my database table. The code that I am using now is :

Dim dr21 As SqlDataReader = myCommand.ExecuteReader()
If dr21.HasRows Then
Do While dr21.Read()
Category = dr21.Item(dr21.FieldCount - 1)
documentFilePath = System.Text.Encoding.Unicode.GetString(Category)
GetDocument(documentFilePath)
Loop
End If

And it is not throwing an error during the Do While Loop but it throws an error in the 1st line of the GetDocument sub procedure on the line :


fs = New FileStream(filePath, FileMode.Open, FileAccess.Read)

The error is : "Illegal characters in path." and I think that it could be due to the variable filePath which has been passed to the sub procedure as "??" and has not been accepted by the FileStream.

Hopefully you can help as I am really in need!
Thanks so much,
Orla