Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to retrieve last inserted records

Author  Topic 

Abid

110 Posts

Posted - 2013-04-01 : 05:51:25
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

52326 Posts

Posted - 2013-04-01 : 05:53:56
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

110 Posts

Posted - 2013-04-01 : 06:08:57
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-01 : 06:32:59
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

52326 Posts

Posted - 2013-04-01 : 06:38:43
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

110 Posts

Posted - 2013-04-01 : 09:56:09
@ 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

52326 Posts

Posted - 2013-04-01 : 10:01:32
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

110 Posts

Posted - 2013-04-01 : 21:21:01
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

52326 Posts

Posted - 2013-04-02 : 01:21:15
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

110 Posts

Posted - 2013-04-02 : 05:45:40
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

110 Posts

Posted - 2013-04-02 : 05:49:16
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

52326 Posts

Posted - 2013-04-02 : 07:08:56
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

110 Posts

Posted - 2013-04-02 : 11:16:05
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

52326 Posts

Posted - 2013-04-02 : 11:49:07
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-02 : 13:10:15
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

110 Posts

Posted - 2013-04-02 : 21:29:13
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

52326 Posts

Posted - 2013-04-03 : 01:39:12
I prefer 2nd one.

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-03 : 06:00:00
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

52326 Posts

Posted - 2013-04-03 : 07:07:33
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

110 Posts

Posted - 2013-04-03 : 12:05:35
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

52326 Posts

Posted - 2013-04-03 : 13:45:09
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
  Previous Page&nsp;  Next Page

- Advertisement -