Exporting VB6 data to PDF using SQL select query from MS Access

30 pts.
Tags:
Microsoft Access
SQL Select
Visual Basic 6
Hi my name is vishal. I think I have a problem in exporting VB6 data to PDF using SQL select query (join query from multiple tables). I have a form in which when user selects a GRNno from combobox and then press button with caption: Generate it should generate a PDF report regarding GRNno. Given below is code in VB6:
Private Sub cmdGenerate_Click()
If cboGRNno.ListIndex = -1 Then
        MsgBox "Please select the GRN No", vbInformation
    End If
    If (cboGRNno.Text <> "") Then
    MDIForm1.loadReportinPDF "select iw.agn,ih.item_name,iw.no_of_bundle,sw.rack_name,sw.store_no from Inward iw,item_master ih,store_master sw where iw.staus=sw.status and iw.agn="&cboGRNno.ListIndex", "STOCK PURCHASE"
    End If
    
End Sub

Private Sub Form_Load()
 On Error GoTo ErrH
    loadGRN
    Exit Sub
ErrH:
    MsgBox Err.Description, vbCritical
End Sub
Private Sub loadGRN()
On Error GoTo ErrH
    Dim vSQLStr As String
    vSQLStr = "Select * from Inward"
    Dim oRS As New ADODB.Recordset
    If (adoDatabase.State = 0) Then
        adoDatabase.Open
    End If
    oRS.Open vSQLStr, adoDatabase, adOpenForwardOnly, adLockReadOnly
    cboGRNno.Clear
    Do While Not oRS.EOF
    cboGRNno.AddItem oRS.Fields("agn").Value
     oRS.MoveNext
    Loop
    oRS.Close
    Exit Sub
ErrH:
    MsgBox Err.Description, vbCritical
End Sub
The form loads required GRNno that has no problem but here is the line in code where i get error:
MDIForm1.loadReportinPDF "select iw.agn,ih.item_name,iw.no_of_bundle,sw.rack_name,sw.store_no from Inward iw,item_master ih,store_master sw where iw.staus=sw.status and iw.agn=" & cboGRNno.ListIndex, "STOCK PURCHASE"

Given below is code for loadReportinPDF:
Sub loadReportinPDF(vSQLStr As String, reportTitle As String, Optional subTitle As String)
    On Error GoTo ErrH
    Dim oRS As New ADODB.Recordset
    Dim oRS1 As New ADODB.Recordset
    If (adoDatabase.State = 0) Then
        adoDatabase.Open
    End If
    oRS.Open vSQLStr, adoDatabase, adOpenForwardOnly, adLockReadOnly
    If oRS.EOF = True Then
        MsgBox "There is no datas for " & reportTitle, vbInformation
        Exit Sub
    End If
    Dim currRow As Double
    Dim dPatientName As String
    Dim dMstr As String
    dPatientName = ""
    
    Dim objPDF As New mjwPDF
    Dim dFileStr As String
    ' Set the PDF title and filename
    objPDF.PDFTitle = reportTitle
    dFileStr = getReportFileName()
    objPDF.PDFFileName = dFileStr
    
    ' We must tell the class where the PDF fonts are located
    objPDF.PDFLoadAfm = App.Path & "Fonts"
    
    ' View the PDF file after we create it
    objPDF.PDFView = False
    objPDF.PDFPageWidth = 841.89
    objPDF.PDFPageHeight = 595.28
    
    ' Begin our PDF document
    objPDF.PDFBeginDoc
        
        objPDF.PDFImage App.Path & "header.jpg", 5, 1, 22, 7
        objPDF.PDFSetFont FONT_ARIAL, 16, FONT_BOLD
        
        objPDF.PDFSetTextColor = vbBlack
    
        ' Set the text we want to print
        objPDF.PDFTextOut reportTitle, 6, 4
        
        If IsMissing(subTitle) = False Then
            objPDF.PDFSetFont FONT_ARIAL, 14, FONT_BOLD
            objPDF.PDFTextOut subTitle, 6.5, 5
        End If
        
        objPDF.PDFSetDrawColor = vbGrayed
        objPDF.PDFTextOut "Stock Register", 2, 7.6
        
        objPDF.PDFTextOut "", 23, 7.6
        
        
'        objPDF.PDFDrawRectangle 1.95, 7, 25.28, 18
        objPDF.PDFSetLineWidth = 0.01
        objPDF.PDFDrawLineHor 1.95, 8.2, 25.28
        ' Set the font name, size, and style
        objPDF.PDFSetFont FONT_ARIAL, 8, FONT_BOLD
        
        ' Set the text color
        objPDF.PDFSetTextColor = vbBlack
    
        ' Set the text we want to print
        objPDF.PDFTextOut "Date", 1, 9
        objPDF.PDFTextOut "Item Code", 3, 8.6
        objPDF.PDFTextOut "Item Name", 3, 9
        objPDF.PDFTextOut "Store", 7.2, 8.6
        objPDF.PDFTextOut "Rack name", 7.2, 9
        objPDF.PDFTextOut "From/To", 11.5, 9
        objPDF.PDFTextOut "IN", 14.3, 9
        objPDF.PDFTextOut "OUT", 15.8, 9
        objPDF.PDFTextOut "GRN No", 17.3, 9
        objPDF.PDFTextOut "Verification", 19.5, 9
        objPDF.PDFTextOut "Supplier name", 22.5, 9
    
        objPDF.PDFSetLineWidth = 0.01
        objPDF.PDFDrawLineHor 1.95, 9.2, 25.28
    ' End our PDF document (this will save it to the filename)
    currRow = 10
    
    Dim minL As Long
    Do While Not oRS.EOF
         '// Do something with the data'
        objPDF.PDFSetFont FONT_ARIAL, 8, FONT_NORMAL
        objPDF.PDFSetTextColor = vbBlack
        objPDF.PDFSetTextColor = vbBlack
         
         dMstr = ""
         If IsNull(oRS.Fields(1).Value) = False Then
            dMstr = oRS.Fields(1).Value
         End If
         If IsNull(oRS.Fields(2).Value) = False Then
            dMstr = dMstr & " - " & oRS.Fields(2).Value
         End If
         dPatientName = ""
         If (dPatientName = dMstr) = False Then
            dPatientName = dMstr
            objPDF.PDFTextOut dMstr, 3, currRow
         End If
         If IsNull(oRS.Fields(0).Value) = False Then
            objPDF.PDFTextOut Format(oRS.Fields(0).Value, "dd/MMM/yyyy"), 1, currRow
         End If
         
         
         dMstr = ""
         If IsNull(oRS.Fields(3).Value) = False Then
            dMstr = oRS.Fields(3).Value
         End If
         If IsNull(oRS.Fields(4).Value) = False Then
            dMstr = dMstr & " - " & oRS.Fields(4).Value
         End If
         
         If dMstr <> "" Then
            objPDF.PDFTextOut dMstr, 7.2, currRow
         End If
         
         
        If IsNull(oRS.Fields(5).Value) = False Then
            If oRS.Fields(5).Value = 1 Then
                objPDF.PDFTextOut "PROD", 11.7, currRow
            ElseIf oRS.Fields(5).Value = 0 Then
                objPDF.PDFTextOut "INW", 11.7, currRow
            ElseIf oRS.Fields(5).Value = 3 Then
                objPDF.PDFTextOut "SER", 11.7, currRow
            ElseIf oRS.Fields(5).Value = 4 Then
                objPDF.PDFTextOut "NPD", 11.7, currRow
            ElseIf oRS.Fields(5).Value = 5 Then
                objPDF.PDFTextOut "TEST", 11.7, currRow
            End If
        End If
        If IsNull(oRS.Fields(6).Value) = False Then
            objPDF.PDFTextOut oRS.Fields(6).Value, 14.3, currRow
        Else
            objPDF.PDFTextOut " 0", 14.3, currRow
        End If
        
        If IsNull(oRS.Fields(7).Value) = False Then
            objPDF.PDFTextOut oRS.Fields(7).Value, 16, currRow
        Else
            objPDF.PDFTextOut " 0", 16, currRow
        End If
'        objPDF.PDFTextOut oRS.Fields("disinfectant").Value, 18, currRow
        If IsNull(oRS.Fields(8).Value) = False Then
            objPDF.PDFTextOut oRS.Fields(8).Value + 1, 18, currRow
        End If
        dMstr = ""
         If IsNull(oRS.Fields(9).Value) = False Then
            dMstr = oRS.Fields(9).Value
         End If
         objPDF.PDFTextOut dMstr, 19.5, currRow
         
        If IsNull(oRS.Fields(10).Value) = False Then
             objPDF.PDFTextOut oRS.Fields(10).Value, 22.6, currRow
        End If
        objPDF.PDFSetLineWidth = 0.01
        objPDF.PDFDrawLineHor 1.95, currRow + 0.2, 25.28

         currRow = currRow + 0.7
         oRS.MoveNext
         If (currRow > 19) And oRS.EOF = False Then
            objPDF.PDFSetLineWidth = 0.01
            objPDF.PDFDrawRectangle 1.95, 7, 25.28, (currRow - 7.7) + 0.2
            objPDF.PDFSetLineWidth = 0.01
            objPDF.PDFDrawLineVer 3.95, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 8.15, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 12.45, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 14.95, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 16.4, 8.2, (currRow - 8.7)
            'objPDF.PDFDrawLineVer 17.15, 8.2, (currRow - 8.7)
            'objPDF.PDFDrawLineVer 17.65, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 18.15, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 20.2, 8.2, (currRow - 8.7)
            objPDF.PDFDrawLineVer 23.2, 8.2, (currRow - 8.7)
            objPDF.PDFEndPage
            
            objPDF.PDFNewPage
            
            dPatientName = ""
            
            objPDF.PDFSetLayoutMode = LAYOUT_CONTINOUS
            
                objPDF.PDFImage App.Path & "header.jpg", 5, 1, 22, 7
                objPDF.PDFSetFont FONT_ARIAL, 16, FONT_BOLD
                
                objPDF.PDFSetTextColor = vbBlack
            
                ' Set the text we want to print
                objPDF.PDFTextOut reportTitle, 6, 4
                If IsMissing(subTitle) = False Then
                    objPDF.PDFSetFont FONT_ARIAL, 14, FONT_BOLD
                    objPDF.PDFTextOut subTitle, 6.5, 5
                End If
                
                objPDF.PDFSetDrawColor = vbGrayed
                objPDF.PDFTextOut "Stock Register:", 2, 7.6
                
                
                
        '        objPDF.PDFDrawRectangle 1.95, 7, 25.28, 18
                objPDF.PDFSetLineWidth = 0.01
                objPDF.PDFDrawLineHor 1.95, 8.2, 25.28
                ' Set the font name, size, and style
                objPDF.PDFSetFont FONT_ARIAL, 8, FONT_BOLD
                
                ' Set the text color
                objPDF.PDFSetTextColor = vbBlack
            
                ' Set the text we want to print
                objPDF.PDFTextOut "Date", 1, 9
                objPDF.PDFTextOut "Item Code", 3, 8.6
                objPDF.PDFTextOut "Item Name", 3, 9
                objPDF.PDFTextOut "Store", 7.2, 8.6
                objPDF.PDFTextOut "Rack name", 7.2, 9
                objPDF.PDFTextOut "From/To", 11.5, 9
                objPDF.PDFTextOut "Debit", 14.3, 9
                objPDF.PDFTextOut "Credit", 15.8, 9
                objPDF.PDFTextOut "GRN No", 17.3, 9
                objPDF.PDFTextOut "Verification", 19.5, 9
                objPDF.PDFTextOut "Supplier name", 22.5, 9
            
                objPDF.PDFSetLineWidth = 0.01
                objPDF.PDFDrawLineHor 1.95, 9.2, 25.28
            ' End our PDF document (this will save it to the filename)
            currRow = 10
         End If
    Loop
    
    objPDF.PDFSetLineWidth = 0.01
    objPDF.PDFDrawRectangle 1.95, 7, 25.28, (currRow - 7.7) + 0.2
    objPDF.PDFSetLineWidth = 0.01
    objPDF.PDFDrawLineVer 3.95, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 8.15, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 12.45, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 14.95, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 16.4, 8.2, (currRow - 8.7)
    'objPDF.PDFDrawLineVer 17.15, 8.2, (currRow - 8.7)
    'objPDF.PDFDrawLineVer 17.65, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 18.15, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 20.2, 8.2, (currRow - 8.7)
    objPDF.PDFDrawLineVer 23.2, 8.2, (currRow - 8.7)
    objPDF.PDFEndDoc
    oRS.Close
    DoEvents
    Dim r As Long, msg As String
    r = StartDoc(dFileStr)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                msg = "File not found"
            Case SE_ERR_PNF
                msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                msg = "Access denied"
            Case SE_ERR_OOM
                msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                msg = "DLL not found"
            Case SE_ERR_SHARE
                msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                msg = "DDE busy"
            Case SE_ERR_NOASSOC
                msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                msg = "Invalid EXE file or error in EXE image"
            Case Else
                msg = "Unknown error"
        End Select
        MsgBox msg
    End If
    Exit Sub
ErrH:
    MsgBox Err.Description, vbCritical
When I execute the query get error saying:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters Expected1.
Can anyone tell me where have gone wrong in the query ? Reply please. Any help would be greatly appreciated.

Software/Hardware used:
vb6,ms access.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The thing is that you need to declare the text you want to write as a variable.
First, use the following in the declarations section:

Dim textinput as String

Now add the following into the sub for a text box (here we named the textbox textbox1):

textinput = Text1.Text

Now, when we call the PDF class to write the variable, we use:
objPDF.TextOut textinput,x,y
objPDF.TextOut_
”  ”

We reiterate this as many times using as many dffirent variables as we need. In so far as I’ve discovered, theres no way to wrap to a new line with this class, so you have to call the TextOut class function for EVERY LINE you want to write. The second line above writes a blank line to the document. Just make sure you keep the space in between the double quotes.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Hi Ametuer.

    I don't think the query itself is causing the error you are seeing, although I can see that it includes 3 tables in the from clause, but you are only joining two of them, so you will probably get a cartesian product as a result.

    Since you said that the line of code that is causing the error is the call to the loadReportinPDF procedure, and the error is being reported by the ODBC driver (which is not used in that line of code) I suppose the error is being caught by your 'ErrH' error handler, and that is preventing you from seen what is the real line of code causing the error.

    The first thing I would try would be removing the 'On Error GoTo' statement, so the application errors out exactly where the error occurs.
    83,285 pointsBadges:
    report
  • Ametuer
    Respected sir
    All i want is to write a sql query which when executed should display agn values from Inward table,item name values from item_master table,store_no values from store_master table,rack_name values from store_master table and no_of_bundle values from Inward table.

    table name:Inward

    column Name                      DataType

    mfr_ref_number                     text

    no_of_bundle                       integer

    supplier_id                           integer

    DC_date                            date/time

    INV_date                          date/time

    row_upd_date                  date/time

    courier_name                     text

    inward_type                      text

    user_id                          long(contains 0 as value for all entries)

    DC_NO                           text

    closed_status                  boolean(true for some agn only)

    to_dept                           text

    agn                                long

     

    table name:item_master

    Column Name                    DataType

    user_id                             long(contains 0 as value for all entries)

    row_upd_date                   date/time

    deleted_status                  false

    item_name                       text

    item_parent                    long

    item_price                      double

    item_desc1                      memo

    item_desc2                     memo

    item_desc3                     memo

    item_type                       long

    item_code                      text

    agn                              long

    measure_type               text

     

    table name:store_master

    Column Name                       DataType

    rack_name                             text

    rack_desc                             memo

    user_id                                 long(contains 0 as value for all entries)

    row_upd_date                       date/time

    status                                 boolean

    store_no                              long.


    table name: Stock_Register

    Column Name                             DataType

    row_upd_date                             date/time

    technician_id                                  long

    user_id                                        long(contains 0 as value for all entries)

    in_out_type                                Integer

    status                                       boolean

    deleted_status                           boolean

    item_id                                       long

    qty_debit                                  double

    qty_credit                                 double

    in_out_id                                   long

    store_no                                    long(contains 0 or 2 as it's value)

    ref_id                                       long

    sl_no                                         text(contains null value or 1 as value to some entries)

    I want to tell you that agn values from Inward table are totally different from agn values from item_master table.

    i have written a query by myself but i am getting partial output only .Given below is my query:

    select i.no_of_bundle,sm.rack_name,sm.store_no,sr.in_out_type,i.agn,it.item_name from inward i,store_master sm,stock_register sr,item_master it where sm.status=true and sr.in_out_id=i.agn and sr.status=true and sm.store_no=sr.store_no

    Can anyone point of where am i going wrong and what should be mended in Where clause of my sql select query? Can anyone help me please.
    30 pointsBadges:
    report
  • bakertaylor28
    The problem is that with the PDF class, you need to call the variable to write WITHOUT quotes. See above for correct use of the TextOut function with respect to variables.
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: