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.


 
  Normal
  0
  
  
  
  
  false
  false
  false
  
  EN-US
  X-NONE
  X-NONE
  
   
   
   
   
   
   
   
   
   
  
  
   
   
   
   
   
   
   
   
   
   
   
  

 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 


 /* Style Definitions */
 table.MsoNormalTable
	{mso-style-name:"Table Normal";
	mso-tstyle-rowband-size:0;
	mso-tstyle-colband-size:0;
	mso-style-noshow:yes;
	mso-style-priority:99;
	mso-style-parent:"";
	mso-padding-alt:0in 5.4pt 0in 5.4pt;
	mso-para-margin-top:0in;
	mso-para-margin-right:0in;
	mso-para-margin-bottom:10.0pt;
	mso-para-margin-left:0in;
	line-height:115%;
	mso-pagination:widow-orphan;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";
	mso-ascii-font-family:Calibri;
	mso-ascii-theme-font:minor-latin;
	mso-hansi-font-family:Calibri;
	mso-hansi-theme-font:minor-latin;
	mso-bidi-font-family:"Times New Roman";
	mso-bidi-theme-font:minor-bidi;}




Software/Hardware used:
vb6,ms access.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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.
    69,835 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

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following