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 yesSELECT * 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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.
|
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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)" |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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.ieSELECT * FROM SaleInfo WHERE Sale_Date = ( SELECT MAX(Sale_Date) FROM SaleInfo)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 recordSET @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 :)CheersMIK |
|
|
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)" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 01:39:12
|
I prefer 2nd one.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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.8302013-04-03 14:55:04.7802013-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 @tabAny how if it already worked for you then even better :) ...CheersMIK |
|
|
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 thisThird, 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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? |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Previous Page&nsp;
Next Page
|