Using PDF Combine with SQL Server Data

We have an application in which textual and image data are stored in a Microsoft SQL Server database. The information is typically retrieved and displayed on a monitor or is printed out.

PDF Combine is used to generate merged PDF files that contain two or more PDF files.

The following Visual Basic code provides examples of some of the required steps

The SQL Server textual data are converted from RTF or TXT formats to PDF using a ComponentOne print module that outputs code to a Microsoft operating system supplied Adobe PDF driver rather than a regular printer driver. This is setup by the following code. The replace instructions are used to circumvent some problems that we had with imbedded blanks and hyphens. (The problems could not be consistently resolved by placing the file names in quotation marks).

It is also necessary to store the name of the PDF file in the registry. Additionally, the name is written to a List.txt file that controls the sequence in which the PDF files are merged.


Dim ps As New PrinterSettings

'Process blanks and hyphens
strTitle = Trim(strTitle)
strTitle = Microsoft.VisualBasic.Replace(strTitle, " ", "_"
strTitle = Microsoft.VisualBasic.Replace(strTitle, " - ", "_"
strTitle = Microsoft.VisualBasic.Replace(strTitle, " ", "_"

'Write the file name to the registry and set the PrinterSettins to Adobe PDF
defaultString = "D:\PDFFiles\" & initials & strText & strTitle & "_" & documentNumber & ".pdf"
SetupRegistryFileName(defaultString, errorCode)
If errorCode > 0 Then Exit Sub
'Write the file name to the list that determines the merge sequence.
Try
sw.WriteLine(defaultString)
Catch err As Exception
MessageBox.Show("Error: " & err.Message)
sw.Close()
Exit Sub
End Try

'Setup the registry entry and printer options
Private Sub SetupRegistryFileName(ByVal defaultString As String, ByVal errorCode As Integer)
errorCode = 0
Try
Registry.SetValue(keyName, "C:\Windows\splwow64.exe", defaultString, RegistryValueKind.String)
Catch ex As Exception
MessageBox.Show(ex.Message, "Registry keyName Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
errorCode = 1
Exit Sub
End Try
ps.PrinterName = "Adobe PDF"
If Not ps.IsValid Then
MessageBox.Show("Invalid Adobe PDF Name"
errorCode = 2
Exit Sub
End If
End Sub


This code converts SQL Server binary image data to PDF.

Dim mstream As New ADODB.Stream
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
mstream.Type = adTypeBinary
mstream.Open()
mstream.Write(rs.Fields("imageData".Value)
mstream.SaveToFile("D:\PDFFilesTemp\Temp1.pdf", adSaveCreateOverWrite)
Catch err As Exception
MessageBox.Show(err.Message, "Error: PDFForm PDFCombine", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Exit Sub
End Try


This code merges the individual PDF files.

strtext = "D:\PDFFiles\" & MainForm.initials & strDate & "CombinedPDF" & ".pdf " & " -List" & " D:\PDFFiles\List.txt" & " -do -content"


Dim timeOut As Integer = 10000
Dim proc As New System.Diagnostics.Process()
Try
proc = Process.Start("PDFCombine.exe", strtext)
Catch err As Exception
MessageBox.Show(err.Message, "Error: Load PDFCombine", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Exit Sub
End Try

proc.WaitForInputIdle()
'Wait for the process to exit.
proc.WaitForExit(timeOut)
'HasExited is true if the application closed before the time-out.
If proc.HasExited = False Then
'Process is still running.
'Test to see if process is hung up.
If proc.Responding Then
'Process was responding; close the main window.
proc.CloseMainWindow()
Else
'Process was not responding; force the process to close.
proc.Kill()
End If
MessageBox.Show("PDFCombine Not Responding. Exiting merge Program"
Me.Close()
End If

There are probably multiple ways to process the textual and image data. Hopefully, these code fragments will be helpful for some users.
Incidentally, the program contains code to send the individual or merged PDF data by e-mail. That module uses ChilkatDotNet which is a flexible, reliable imbedded e-mail program.

Dear Sir or Madam,

Thank you very much for your comments. I have sent everything to our developers. They will work with this issue.