[CR XI] - How to avoid printing twice ???

[LuxCoder]

I have to print orders for a particular selected date ( say 25th Aug 07), now if i take the prints today(20th Aug 07) and there are more orders placed tomorrow for (25th Aug), how do i take the prints of newly booked orders only and not the old ones ??? i.e. i need to avoid taking print twice !

How can i do this ???

[Besoup]

I would set up a flag in your database on whether or not the order has been printed. When you call your routine to print them just mark that field printed.

[LuxCoder]

How can i set up flag in the database... I am using Oracle 9i database. Is there any facility in the CR XI for the same purpose ?

[Besoup]

How can i set up flag in the database... I am using Oracle 9i database. Is there any facility in the CR XI for the same purpose ?

It would just be an added field (ie. Order_Printed) to your order table. When the order is created populated the new field with something like an "N". When you call your routine to print the order, change it to a "Y". Only have your crystal report print orders that Order_Printed = "N". Not sure how you are calling your print routines but it is probably the most practical way to go about it.

[LuxCoder]

okay... anymore ideas apart from this ???

[LuxCoder]

anyone ???

[wes4dbt]

To be safe you should check if everything printed correctly before you mark the records as printed.

If MsgBox("Did Everything Print Correctly?", vbYesNo + vbQuestion, "Data Report") = vbYes Then
    ' go mark records
endif

[Pasvorto]

Logic would dictate that the most efficient way you are going to be able to tell if a record has already printed is to have a ALREADY_PRINTED flag in the database for that record.

Of course, there are always other ways. You could have a separate table in the database listing <some key> of all the records that have been printed. Then, when you print you can check that table and see if the record has already printed.

[LuxCoder]

i am viewing and sorting the required data in Crystal Reports so how can i mark these already printed data in the Database ? Through VB code or CR ? In both case how ???

[Pasvorto]

I think you may have to do what wes4bt said. Use your vb code to call the report using a parameter of PRINT_FLAG not set. Once the report finishes, ask if everything is OK. If it is, use the same paramter in a SQL_UPDATE to update the database to reflect the new status.

I think I read somewhere that CR can update the db, but I don't know how.

You might try the CR forum also

http://support.businessobjects.com/f...=5&ps=25&pn=1&

[Besoup]

i am viewing and sorting the required data in Crystal Reports so how can i mark these already printed data in the Database ? Through VB code or CR ? In both case how ???

Do it in the same vb code that you are using to call your report. just add a function that will mark a flag in your database as being printed. when you pull the data for your report use the flag as criteria on which you should print the data or not.

[GaryMazzone]

This is probaly the best way to do this (a printed flag on the row). But be carefull, once you garther the data (based on a select) the data can change based on someone adding new data or updateing some line that requires another print. You want to be carefull not to mark those rows as printed.

[LuxCoder]

I have no idea on how to set up flag in the database... it's like my query is done in Crystal Reports now and not in the VB form... I think for the afore mentioned solution i will have to pass the query through VB form...

I am querying upon the Delivery date of my recordset through CR. Can anyone tell me how do i pass this query from Vb form and mark the flag 'P'-Printed 'NP'-Not Printed to the field 'Print' in datatable 'Booking' ?

Codes would be helpful.

[LuxCoder]

Any one ???

[GaryMazzone]

You can't do it from CR. To mark the row as printed you must do it from VB. You could run a query that gets the PK's of all rows to be printed. Send that to CR as a recordselection criteria. Once the report has printed you use the same set of PK to update the Oracle table from VB and set the printed column to whatever you have decided to mark it as to show it has been printed.

[LuxCoder]

what's PK ? can u give a code example for it ?

[GaryMazzone]

PK is the primary key. Do you know SQL? Do you know how to pass a Selection Formula to CR?

[LuxCoder]

no i dont know ! Plz help ! :-(

[GaryMazzone]

Don't know what SQL? How to call CR report? What do you have so far? I can't write it all from scratch for you.

[LuxCoder]

Well, this is what i have so far. I have filtered the required data according to field 'Delivery Date' in the CR. Now what i want is to pass the query from the VB and mark them printed in the database.

Option Strict On
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
 
Public Class WorkPrint
 
    Private Sub WorkPrint_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Master.btnact = "Z"
        Master.NewButton.Enabled = True
        Master.EditButton.Enabled = True
        Master.SaveButton.Enabled = False
        Master.DeleteButton.Enabled = True
        Master.UndoButton.Enabled = True
        Master.RedoButton.Enabled = True
        Master.FirstButton.Enabled = True
        Master.BackButton.Enabled = True
        Master.NextButton.Enabled = True
        Master.LastButton.Enabled = True
        Master.CloseButton.Enabled = True
        Master.SearchButton.Enabled = True
    End Sub
 
    Private Sub WorkPrint_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ConfigureCrystalReports()
    End Sub
 
    Private Sub ConfigureCrystalReports()
 
 
        Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
        myConnectionInfo.ServerName = "orcl"
        myConnectionInfo.DatabaseName = "sun"
        myConnectionInfo.UserID = "scott"
        myConnectionInfo.Password = "tiger"
 
        SetDBLogonForReport(myConnectionInfo)
 
    End Sub
    Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo)
 
        Dim myTableLogOnInfos As TableLogOnInfos = CrystalReportViewer1.LogOnInfo()
        For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
            myTableLogOnInfo.ConnectionInfo = myConnectionInfo
        Next
 
    End Sub
End Class

[GaryMazzone]

As I said earlier you can not update the database from CR. You must write the update in VB and perfrom it though an Oracle Connection object.

[LuxCoder]

can u help me with codes ?

[GaryMazzone]

What are the field? How do you connect to the Oracle database?

[LuxCoder]

This is an example of how i connect to db...

crs.Open("select * from pmeasurement where cuid ='" & ChildForm.Text2.Text & "'", Module1.db, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

I have to filter upon delivery date 'dlvrdt' which is selectable from datetimepicker.

[GaryMazzone]

Are you using VB6 or .Net? I assume from the ADODB reference that this is VB6. What is cuid? What are the field names you really want? You shouldn't just be bring everyting back if you don't really need it.

[LuxCoder]

i am using .net for desktop application... cuid is the customer id... that was an example for connection but i want to filter the pmeasurement for delivery date.

[GaryMazzone]

If you are using .Net you should be using ADO.Net not the older ADODB database access functions. So if cuid is the customer id what is the primary key for the table? What is the delivery date field named? What is the CR name? How are you calling CR?

Your code above never shows the call to display or print the CR.

[LuxCoder]

I came to know about ADO.net recently when i have almost finished my project... now i cannot rectify all of them. Delivery date field is 'dlvrdt' and the CR name is Workorder.rpt. I have attached this report to the CR viewer in my form which opens the respective report on load event.

[GaryMazzone]

Quick and not tested

Dim strSQL As String
strSQL = "Select PrimaryKeyField From  pmeasurement where Printed = 'N' Or Printed Is NULL"
crs.Open strSQl,Module1.db, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic
 
If Not crs.BOF and Not crs.EOF Then
  Dim strFiter As String
  strFilter = {PrmaryKeyField} In ["
  Do Unitl crs.EOF
       strFitler = strfilter & crs.Fields(0).Value & ","
       crs.MoveNext
  Loop
  strFitler = Left(strFilter,Len(strFilter -1)) & "]"
  'your  cr reprort
   crReport.RecordSelectionFormula = strFitler
   'Show you report.
   'After Report Done
   strFilter = strFilter.Replace("[","(").Replace("]".")")
   strsql = "Update pmeasurement Set Printed = 'Y' Where PrimaryKeyField  " & the strFitler var (taking out the part bewennd {}
   the ADODB.Connection.Execute strSQL
End If

[LuxCoder]

Thanks for the codes.

I want to show you complete scenario of what i am trying to achieve

I have three measurement tables in Oracle DB which contains customer's measurement according to Customer ID (Cuid):

1. Pmeasurement
2. Smeasurement
3. Cmeasurement &
4. Jmeasurement

I have a table named 'Order' in which i have booking details of customer out of which we require the following fields:

1. Cuid (Customer ID)
2. Orderno (Order Number)
3. DelDate (Delivery Date)

In crystal reports i have WorkOrder.rpt which consists of four subreports i.e. Pmeasurement, Smeasurement, Cmeasurement & Jmeasurement. Now, i have a parameter set to DelDate in 'Order' (Oracle DB) which further filters the measurements of that particular customer whose order is booked on 'DelDate'. As a result, WorkOrder.rpt gives me the measurements of all the customers whose delivery is due on a particular date.

Now i want to mark the orders in 'Order' table in DB as 'Printed' (Y/N). In CR, the parameter is already set as DeliveryDate. How do i pass the query through VB form to select Orders whose Measurements have been printed and then mark them printed or not printed.

[LuxCoder]

Mr.Gary... u there ?

[GaryMazzone]

You can not. REPEAT YOU CAN NOT update the database from CR. You must do this using the application and connect to the database and issue and update statement.