7 Replies - 4445 Views - Last Post: 18 April 2013 - 12:12 AM

#1 Adqusit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 158
  • Joined: 02-March 13

How to fetch the last inserted records from table

Posted 01 April 2013 - 02:50 AM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: How to fetch the last inserted records from table

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: How to fetch the last inserted records from table

Posted 01 April 2013 - 02:20 PM

Assuming that Sale_ID is an auto-incremented field, and that you aren't concerned with people that go back and change their orders, then you can do this:
SELECT *
FROM SaleInfo
WHERE Sale_ID = (
  SELECT MAX(Sale_ID)
  FROM SaleInfo
)


Was This Post Helpful? 0
  • +
  • -

#3 Adqusit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 158
  • Joined: 02-March 13

Re: How to fetch the last inserted records from table

Posted 02 April 2013 - 08:34 AM

I check the query, you provided. Its working all right.

Please give your opinion on the following query.

SELECT * FROM SaleInfo WHERE Sale_Date = (SELECT Top 1 Sale_Date FROM SaleInfo ORDER BY Sale_Date DESC)


What about this query, for my problem?
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: How to fetch the last inserted records from table

Posted 02 April 2013 - 01:53 PM

That query should definitely get the Sales with the latest Sale_Date, though you may get some duplication. In the long run, you're probably better off normalising your data, so that Sales are separate from Sale_Items, like so:

Sales
    Sale_ID
    Sale_Date
    {(Purchaser_ID, Sale-Complete, etc.}


Sale_Items
    Sale_Item_ID
    Sale_ID (FK -> Sales.Sale_ID)
    {Item_ID, Quantity, etc.}

Was This Post Helpful? 0
  • +
  • -

#5 Adqusit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 158
  • Joined: 02-March 13

Re: How to fetch the last inserted records from table

Posted 02 April 2013 - 07:00 PM

But i have a problem. When i fetch many records, so the matching criteria is getting change, because the change in miliseconds is occurring. What to do with this: Please guide me.

As in my application, I'm showing the result both in DGV and Crystal Report as well, so in DGV Sale_Date =
4/1/2013 8:42 PM


and in Crystal Report it appears like this:
4/1/2013 8:42:47 PM


and in DataBase its like this in the Table:
 4/1/2013 8:42:47 PM


Please tell me that how to solve this problem.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: How to fetch the last inserted records from table

Posted 02 April 2013 - 07:24 PM

This is why you should normalise your data, as I showed in my last post. When databases are not normalised, the moment you start doing queries that are non-trivial, you run into problems. Thankfully, this query is still reasonably trivial, so a band-aid solution is to use this:
SELECT *
FROM SaleInfo
WHERE Sale_ID = (
	SELECT TOP 1 Sale_ID
	FROM SaleInfo
	ORDER BY Sale_Date DESC
)


If you want to start adding in other data, or filtering on other predicates, you (or rather, the query optimiser) might start running into trouble.
Was This Post Helpful? 0
  • +
  • -

#7 Adqusit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 158
  • Joined: 02-March 13

Re: How to fetch the last inserted records from table

Posted 03 April 2013 - 02:30 AM

I did not understand even a word in your last post. Please guide me that how I remove this problem.
Was This Post Helpful? 0
  • +
  • -

#8 Adqusit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 158
  • Joined: 02-March 13

Re: How to fetch the last inserted records from table

Posted 18 April 2013 - 12:12 AM

I tried to find the problem in my Table. I found it. It really needs normalization. So far, my project is working and its not a huge one, to give problems. But facts are facts, i can't ignore the basic normalization rules.

I will redress it all.

Thank you very much for your devoted assistance.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1