How to display the last inserted records in crystal report

[ADQUSIT]

Hi. I have a Sales form. I want that when the user sales 1 or more than 1 product to the customer and after pressing the submit button, the record saves in DataBase and Crystal report of those last inserted records will be created. In simple words i want to create a simple Crystal Report, on which i want to show the last inserted records from DataBase.

Please guide me that how to this?

[jggtz]

Normally I use the RecordSelectionFormula when call the report
In your case it could be to use the invoice number
Also, to get a better help it would be better if you provide CR version & language

[ADQUSIT]

Thank you jggtz.
I'm using Visual Studio 2005 and sql server 2005 standard. CR is the builtin, available in VS.
Secondly, there is no such Invoice number, just Primary Key, which is Sale_ID

[jggtz]

Then use Sale_ID as the RecordSelectionFormula

[ADQUSIT]

But How i will determine that what number of records i need to display? this is my question? Lets say i iinsert 5 records in last attempt, so how only last 5 records will be displayed jggtz?

[jggtz]

Following your example... If the last 5 saved records belong to the same Sale operation (same customer) then in your table those records must be identified with something in common (Invoice number or Customer ID or Sale Id or whatever).
If not then it will be not easy

[ADQUSIT]

Hi jggtz. These are my table fields, in which Sale_ID is primary key and Prod_ID is Foreigh Key. Here nothing is common. Please guide me for this.

Sale_ID, Prod_ID, Sale_Prod_Qty, Sale_Prod_PriceOfOne, Sale_Prod_PriceOfAll, Sale_Date

[jggtz]

You have a case of Master-Detail tables
Master -> Sale_Id, Date, Customer, Total, etc
Detail -> Sale_Id, Prod_ID, Qty, Price, LineTotal
Of course you need also the Customers and Products tables
In my opinion the Sale_ID field must be the same for all the same sale operation.
I think You are trying each row as a sale operation

[ADQUSIT]

Hi. I read post # 6 and now i got it. You mean that if i have something common for records then it would be very easy to get the desired output right. So in my table Sale_Date has a uniqueness.

Lets say if the user inserts 2 records, so according to my coding, all these 2 records will be inserted with single insert statement right, so for all these 2 records, the time second will be the same and thus i would search in database that: retrieve all those records which have the latest second of time, so by this way it will display the last inserted record in crystal report??

Give your opinion Please???

[jggtz]

If that's the way you want to follow, I would try hour & minutes only

[ADQUSIT]

using second is just because lets suppose if in a minute a user deals 4 customers each for having 4 4 products, so for dealing 4 customers in a single minute, will be problematic. Therefore, i used seconds. What you say?


Secondly you say that

If that's the way you want to follow

What other way you suggest?

[jggtz]

Sorry for my english...
Let's be clear
As I understand this is a single operation:
-Customer arrives
-Buy 1 coke and 1 bread
To me this is a single sale and must be identified by an ID (the same ID for the 2 rows)
in this way it will be easy to identify and report (among other things)

[ADQUSIT]

To me this is a single sale and must be identified by an ID (the same ID for the 2 rows)

how to have same ID for 2 rows? Please explain.

[jggtz]

You have a case of Master-Detail tables
Master -> Sale_Id, Date, Customer, Total, etc
Detail -> Sale_Id, Prod_ID, Qty, Price, LineTotal
Of course you need also the Customers and Products tables
In my opinion the Sale_ID field must be the same for all the same sale operation.
I think You are trying each row as a sale operation

You may search for Master-Detail tables design

[ADQUSIT]

@ Jggtz, do you refer Master-Detail table, to a Table with solid fields, won't be changed, but its fields will be used in other tables? No changes will be made in Master table, and data will be fetched from that master table to the child tables? Am i right with this?

[jggtz]

No
Master is one table and Detail is another table
In Master table you will save : Sale_Id, Date, Customer, Total, etc. all the header information
In Detail table you will save : Sale_Id, Prod_ID, Qty, Price, LineTotal, etc. all the detail information of each row of sale
You see that the Sale_Id field is common both tables and is incremented by one each time a sale is realized

[ADQUSIT]

@ jggtz, what you mean by

all the header information

I'm not understanding this master and detail tables scenario that how to do this?

[jggtz]

Let's say this is a sale operation:


This goes in Master table
Sale ID : 123
Date : 23 / 03 / 2013
Customer : 321 Mr. Henry Flowers
Total Sale : $7.50

this goes in Detail table
Sale ID:123 Qty.: 2 Item: 1922837 Description: Can of coke Price: $1.00 Total: $2.00
Sale ID:123 Qty.: 3 Item: 2567885 Description: Piece of xxx Price: $1.00 Total: $3.00
Sale ID:123 Qty.: 5 Item: 1925332 Description: Candies abc Price: $0.50 Total: $2.50

Common field is Sale ID

[ADQUSIT]

Oh, for this purpose, i need to change a massive modification in my design as well as Coding. Right. even my Analysis as well.

[jggtz]

We have 5 phases in Program development
1. PROBLEM ANALYSIS PHASE
2. PROGRAM DESIGN PHASE
3. CODING PHASE
4. TESTING AND DEBUGGING PHASE
5. DOCUMENTATION PHASE

This problem must have been resolved in phase 2 and I'm not saying that my way is the only way
It's very dificult to resolve design failures in coding phase and the result is a poor program
So could be a right time to check

[ADQUSIT]

Very Well. This time i'll be going with Post # 9, which validly solves my problem and its going all right with my application, so far. I haven't deployed it on client machine, but on my development PC its going all right along.

But before, this I want your opinion once, that whether, they way i adapted in post # 9 is right in your opinion?

[jggtz]

Not 100% right
What will happen if the user save 1 record at 14:59:59 and the next at 15:00:35

You can try storing the last saleIDs in an array and pass to CR as a filter

[ADQUSIT]

jggtz

What will happen if the user save 1 record at 14:59:59 and the next at 15:00:35

What will wrong happen with these 2 times?

[jggtz]

If you are sure that the field "time" will have the same hour-min-sec for all the last saved records then go ahead...

[ADQUSIT]

Yes jggtz. I inserted 6 records with one click, so it has the following image result. Please check it and find that is it fine or not?

[jggtz]

It's ok
You could use the date/time field to select the records

[ADQUSIT]

This query is not working jggtz.

Look this is my form crystal report from load event code:

    dsSql2.Clear()

        Dim selSale As String = "select top 1 Sale_Date from SaleInfo order by Sale_ID Desc"
        Dim cmdSql As New SqlCommand(selSale, cnSql)
        cmdSql.CommandType = CommandType.Text
        daSql.SelectCommand = cmdSql
        daSql.Fill(dsSql2, "SaleInfo")

        TextBox1.DataBindings.Add("text", dsSql2, "SaleInfo.Sale_Date")

this will load and bind the last record from DB, to textbox1. Okay.
When textbox wil be loaded with last value, then i will press the button and the following code will be execute.

 cnSql.Open()
        Dim sel As String = "select * from SaleInfo where Sale_Date = '" & TextBox1.Text & "'"
        Dim cmdSql As New SqlCommand(sel, cnSql)
        cmdSql.CommandType = CommandType.Text
        daSql.SelectCommand = cmdSql
        daSql.Fill(dsSql2, "SaleInfo")

        Dim objRpt As New CrystalReport1
        objRpt.SetDataSource(dsSql2.Tables("SaleInfo"))
        CrystalReportViewer2.ReportSource = objRpt
        CrystalReportViewer2.Refresh()
        cnSql.Close()

But when i press the button so it does not load anything? Please tell me that what is wrong wit this.

[jggtz]

Try formating the date/time in textbox as yyyymmdd hh:mm:ss

[ADQUSIT]

it doesn't change, because i tried and the bind data can't be changed.

[jggtz]

We don't want to change it, just to format... try it... or do not use binding

[ADQUSIT]

I set the format to Short and Custom Format to yyyy/mm/dd hh:mm:ss, but its still not working. For cross checking. I added a DataGridView on my form and the new code is:

     Dim sel As String = "select * from SaleInfo where Sale_Date = @Sale_Date"
        Dim cmdSql As New SqlCommand(sel, cnSql)
        cmdSql.CommandType = CommandType.Text
        daSql.SelectCommand = cmdSql

        With cmdSql
            .Parameters.Add("@Sale_Date", SqlDbType.DateTime).Value = DateTimePicker1.Text
        End With

        daSql.Fill(dsSql2, "SaleInfo")

        DataGridView1.DataSource = dsSql2
        DataGridView1.DataMember = "SaleInfo"

        Dim objRpt As New CrystalReport1
        objRpt.SetDataSource(dsSql2.Tables("SaleInfo"))
        CrystalReportViewer2.ReportSource = objRpt
        CrystalReportViewer2.Refresh()
        cnSql.Close()

Now this code displaying the record in DGV, but not in Crystal Report? Whats wrong now?

[jggtz]

yyyymmdd hh:mm:ss
date without /

[ADQUSIT]

Not working either. Neither by design, nor by coding.

Okay let me ask you something (just to make this problem solve).
In my main form called SaleInfo. On this form, the customer sales multiple products with single Insert statement right. Let's say he sells 5, so all 5 records will be inserted into DataGridView and then to DB. right. After this i want that automatically report of those saled products is generated, as a customer Receipt (Likewise, we receive after shopiing from Uitlity Store). What my problem is, that How i will determine on Crystal Report that how many number of records were inserted in last attempt? This is the whole problem, for which i actually need crystal Report.

Did you get my jggtz?

[jggtz]

I got it since first post
But you are walking the hardest path
You need a common field but you don't want to add a new field
You say with the datetime field and had problems... try it harder
It's important to consider the db... if it's SQL or Access or Oracle... then the datetime format will be different in the sql query

[ADQUSIT]

I'm using sql server 2005 standard.

Secondly, if you are with me, so i'll add new field in it. Just guide me through.

[jggtz]

Ok
I'll try to help you to select using that datetime field
Gimme some time and I'll be back with some answer

[jggtz]

Just test in a SQL Server 2005 and I can Select usinf datetime fields using the format as I posted in posts 28 & 32

Please post your actual code where you are selecting the last inserted records using datetime field

[ADQUSIT]

Hi.

I change the query last night and its working now. Here is my new code:

This is my form load event code:

Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = (SELECT Top 1 Sale_Date FROM SaleInfo ORDER BY Sale_Date DESC)"
        Dim cmdSql As New SqlCommand(selSale, cnSql)
        cmdSql.CommandType = CommandType.Text
        daSql.SelectCommand = cmdSql
        daSql.Fill(dsSql2, "SaleInfo")
        DateTimePicker1.DataBindings.Add("text", dsSql2, "SaleInfo.Sale_Date")

And this is my button click event code:

cnSql.Open()
        Dim sel As String = "select * from SaleInfo where Sale_Date = @Sale_Date"
        Dim cmdSql As New SqlCommand(sel, cnSql)
        cmdSql.CommandType = CommandType.Text
        daSql.SelectCommand = cmdSql

        With cmdSql
            .Parameters.Add("@Sale_Date", SqlDbType.DateTime).Value = DateTimePicker1.Text
        End With

        daSql.Fill(dsSql2, "SaleInfo")

        DataGridView1.DataSource = dsSql2.Tables("SaleInfo")

        

        Dim objRpt As New CrystalReport1
        objRpt.SetDataSource(dsSql2.Tables("SaleInfo"))
        CrystalReportViewer2.ReportSource = objRpt
        CrystalReportViewer2.Refresh()
        cnSql.Close()

I also have a query; Its also showing my desired output, but I'm not sure that which one is better now, the below one or the above one?

SELECT * FROM SaleInfo WHERE Sale_Date = ( SELECT MAX(Sale_Date) FROM SaleInfo)

[jggtz]

Both are ok

[ADQUSIT]

But I am worry about your post # 22? Please guide me that what is the expected error the user can get?