asked on

wrting multiple PDF's (stored as SQL Images) to a selected folder in VB

I need to write multiple PDF receipts stored as images in a SQL DB to a destination folder.
Below will handle 1 PDF, but I need to handle multiple pdf's to create my invoice.
any ideas? I imagine rewriting the SQL statement to select ReceiptID, BinaryData....and putting it in a Datatable and then reading the DT in a Do/Loop

Dim myconnection As String = My.Settings.LaborSheetsConnectionString
Dim connection As New SqlConnection(myconnection)
Dim strSql As String
connection.Open()
strSql = "Select BinaryData from SubcontractorInvoicing where JobNumber = '" & JobNumberHolder & "' and Fileuploaded = 'True' and ExpenseDate >= '" & BeginDateI & "' and ExpenseDate <= '" & EndDateI & "'"
Dim sqlCmd As New SqlCommand(strSql, connection)
'Get image data from DB
Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())
Dim sTempFileName As String = DestinationFolder_Invoice.Text & "\H_" & intSecond & ".pdf"
'Read image data into a file stream
Using fs As New FileStream(sTempFileName, FileMode.OpenOrCreate, FileAccess.Write)
fs.Write(fileData, 0, fileData.Length)
'Set image variable value using memory stream.
fs.Flush()
fs.Close()
End Using

connection.Close()

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BKennedy2008

ASKER

Awesome. Thanks, taylored it a bit and this works like a champ:

Dim myconnection As String = My.Settings.LaborSheetsConnectionString
Dim connection As New SqlConnection(myconnection)
Dim strSql As String
connection.Open()
strSql = "Select FileName, BinaryData from SubcontractorInvoicing where JobNumber = '" & JobNumberHolder & "' and Fileuploaded = 'True' and ExpenseDate >= '" & BeginDateI & "' and ExpenseDate <= '" & EndDateI & "'"
Dim sqlCmd As New SqlCommand(strSql, connection)
'Get image data from DB
' Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())
Dim rst As SqlDataReader = sqlCmd.ExecuteReader

While rst.Read

Dim fileData As Byte() = rst("BinaryData")
Dim FileName As String = rst("FileName")

Dim sTempFileName As String = DestinationFolder_Invoice.Text & "\H_" & FileName & ".pdf"
'Read image data into a file stream
Using fs As New FileStream(sTempFileName, FileMode.OpenOrCreate, FileAccess.Write)

fs.Write(fileData, 0, fileData.Length)
'Set image variable value using memory stream.
fs.Flush()
fs.Close()
End Using
End While
connection.Close()

BKennedy2008

ASKER

Thanks for the quick response