SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to retrieve last inserted records
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/01/2013 :  05:51:25  Show Profile  Reply with Quote
Hi. I have a sale form SaleInfo, on which i have textboxes and DataGridView. When the user sales multiple products so all records are first loaded into DGV then by insert button, multiple records are being inserted into DB. Right. Now as we get a receipt from Utility Store, after shopping, likewise, I want that when user sales n number of products, so a report of those saled products is generated for the customer.

The problem is that, how i will determine on Crystal Report that how many number of records were inserted with last attempt?

This is my query on form load event.

select top 1 Sale_Date from SaleInfo order by Sale_ID Desc


But this query fetches only single record, but i need to fetch that number of records, which were inserted in last insertion.

Hope my question is not vague?

Please assist me that what to do now?

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/01/2013 :  05:53:56  Show Profile  Reply with Quote
Do you've date of last sale captured somewhere? if yes

SELECT * FROM SaleInfo WHERE Sale_Date > @LastSaleDate

will give you all recent sales

@LastSaleDate should be defined as a parameter and populated with last date of sale from your sales table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/01/2013 :  06:08:57  Show Profile  Reply with Quote
Yes i do have Sale_Date field in Table.

I didn't understand on this Visakh16:

quote:
@LastSaleDate should be defined as a parameter and populated with last date of sale from your sales table.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/01/2013 :  06:32:59  Show Profile  Reply with Quote
To do this reliably, you need a "batchId" column in your SalesInfo table. It could be an identity column. Then, you would query like this:
declare @maxBatchId int;
select @maxBatchId = max(batchId) from SalesInfo;
select * from SalesInfo where batchId = @maxBatchId;

You can combine the 3 statements into one if you like.

You can rely on the datestamp column, but in a multi-user scenario, there is a slight possibility that that will give you incorrect results. Also, depending on how you insert the data - whether as a group, or one row at a time, the datestamp column could give you incorrect results.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/01/2013 :  06:38:43  Show Profile  Reply with Quote
quote:
Originally posted by Abid

Yes i do have Sale_Date field in Table.

I didn't understand on this Visakh16:

quote:
@LastSaleDate should be defined as a parameter and populated with last date of sale from your sales table.



do you want records that got inserted in previous insertion operation or are you looking at playing back what you inserted currently?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/01/2013 :  09:56:09  Show Profile  Reply with Quote
@ Visakh16.

Yes I just need the last inserted records (inserted by last insertion), regarless of how many record were inserted with single statement, 1, 2, 3 4 etc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/01/2013 :  10:01:32  Show Profile  Reply with Quote
you still didnt get what i asked.
My question was is this query going to be fired after a insert operation or is it fired to get records affected in previous insertion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/01/2013 :  21:21:01  Show Profile  Reply with Quote
No. This query executes on form load event, where it fetches to retrieve the last inserted record (s).

select top 1 Sale_Date from SaleInfo order by Sale_ID Desc


But it displays only single record filed in DGV, i.e. Sale_Date, but i need all. So how i will determine that how many number of records were previously inserted.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/02/2013 :  01:21:15  Show Profile  Reply with Quote
then you either need a table capturing last retrieved records datevalue or you need a batchid as James sugested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/02/2013 :  05:45:40  Show Profile  Reply with Quote
Well, I found the solution and the problem was hidden in the query. I change the query and its working amazingly. But as an MVP and other senior members, I want your opinion must on this. Here is my code:

Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = (SELECT Top 1 Sale_Date FROM SaleInfo ORDER BY Sale_Date DESC)"
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/02/2013 :  05:49:16  Show Profile  Reply with Quote
Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = ( SELECT MAX(Sale_Date) FROM SaleInfo)"

This query is also working properly, but i don't know that which one of both is best. Please guide me.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/02/2013 :  07:08:56  Show Profile  Reply with Quote
how will this work? Is Sale_date having time part? whats the guarantee that records inserted in same batch all will have same date value? also what about concurrent inserts by multiple users?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/02/2013 :  11:16:05  Show Profile  Reply with Quote
Well, If you are used to with Sql Server, so it has a dataType, DateTime, which stores Date and Time both.
Secondly, there are no such multiple users this time in my application.
Third, all entries has same time and date, at the time of insertion, by inserting multiple values with single insert statement.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/02/2013 :  11:49:07  Show Profile  Reply with Quote
quote:
Originally posted by Abid

Well, If you are used to with Sql Server, so it has a dataType, DateTime, which stores Date and Time both.
Secondly, there are no such multiple users this time in my application.
Third, all entries has same time and date, at the time of insertion, by inserting multiple values with single insert statement.



I'm used to sqlserver and datetime datatype however you can still write logic to store only datepart in it that was the whole point in asking the question
Now that you tell it has same date and time part for a batch you're saved. Then what you can do is to use your current logic.

ie

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/02/2013 :  13:10:15  Show Profile  Reply with Quote
quote:
Originally posted by Abid

No. This query executes on form load event, where it fetches to retrieve the last inserted record (s).

select top 1 Sale_Date from SaleInfo order by Sale_ID Desc


But it displays only single record filed in DGV, i.e. Sale_Date, but i need all. So how i will determine that how many number of records were previously inserted.



It seems that its kind of master detail (aka parent child) relationship structure. And you're looking for all Detail/Child records against the most recent Master/Parent record? If so then
first find the most recent PrimaryKey ID of the master record
SET @MRPk_ID = (SELECT Top 1 Pk_ID FROM MasterTable Order by SaleDate Desc)
then Get all the detail/Child records against that masterID
SELECT Columns FROM DetailTable WHERE MasterTable.Pk_ID=@MRPk_ID

You can write the query any way you want :)

Cheers
MIK
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/02/2013 :  21:29:13  Show Profile  Reply with Quote
What you say about these queries:

Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = (SELECT Top 1 Sale_Date FROM SaleInfo ORDER BY Sale_Date DESC)"

Dim selSale As String = "SELECT * FROM SaleInfo WHERE Sale_Date = ( SELECT MAX(Sale_Date) FROM SaleInfo)"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/03/2013 :  01:39:12  Show Profile  Reply with Quote
I prefer 2nd one.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/03/2013 :  06:00:00  Show Profile  Reply with Quote
Same do I, but I am afraid that it might not result what you're looking for e.g. "but i need all." as highlighted earlier.

Since the Sale_Date has time portion which would differ in milliseconds for every second,third or fourth record. So if the last batch added has had ten items with a sale_date as

2013-04-03 14:55:04.830
2013-04-03 14:55:04.780
2013-04-03 14:55:04.700
..
..
..
your query would only be coming up with one or two records

declare @tab table (col1 datetime)
insert into @tab values ('2013-04-03 14:55:04.830'),
('2013-04-03 14:55:04.780'),
('2013-04-03 14:55:04.700')
Select Max(Col1) from @tab

Any how if it already worked for you then even better :) ...

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/03/2013 :  07:07:33  Show Profile  Reply with Quote
thats why i asked that in eralier post but OP's reply was this
Third, all entries has same time and date, at the time of insertion, by inserting multiple values with single insert statement.

so i hope its not a problem anymore

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 04/03/2013 :  12:05:35  Show Profile  Reply with Quote
As I've seen that in Sql server, milisecond is not there. It has only 3 values:
hh:mm:ss.

So how this problem will occur? Please guide me. I want to remove it, if it really exists?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/03/2013 :  13:45:09  Show Profile  Reply with Quote
quote:
Originally posted by Abid

As I've seen that in Sql server, milisecond is not there. It has only 3 values:
hh:mm:ss.

So how this problem will occur? Please guide me. I want to remove it, if it really exists?


the problem will occur if above timepart is different for different records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000