Author |
Topic |
Abid
110 Posts |
Posted - 2013-03-15 : 08:49:55
|
Hi. I have a vb.net form, from which i can Insert more than 2 (3, 4, 5 etc) records with one insert statement, right. Now i want to display all those last inserted records in crystal report. My Question is that, Is it possible in SQL (i mean is there any such query) that i could retrieve only last inserted record, whether its 1 or more than 1? |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-03-15 : 10:08:32
|
I take it you mean last inserted records from the VB form? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-15 : 11:27:44
|
do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Abid
110 Posts |
Posted - 2013-03-15 : 11:51:11
|
quote: Originally posted by Grifter I take it you mean last inserted records from the VB form?
Aah, I mean that i want to see the last record actually in my crystal report (last record means that last inserted records whether 1 or more than one). |
|
|
Abid
110 Posts |
Posted - 2013-03-15 : 11:53:26
|
quote: do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?
Yes i have Primary Key which is set to Auto_Increment and data is storing in the main (same) table. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-15 : 12:56:19
|
quote: Originally posted by Abid
quote: do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?
Yes i have Primary Key which is set to Auto_Increment and data is storing in the main (same) table.
then use SCOPE_IDENTITY to get inserted id value. In case of multiple value inserts use OUTPUT clause to get values from INSERTED table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Abid
110 Posts |
Posted - 2013-03-16 : 06:54:42
|
I'm using a simple query i.e.select top 5 * from ProdInfo order by Prod_ID Descthis puts me to get 5 last inserted records, but its not solving my problem. My problem is that I have to shown the last numbers of records (whether 1, 2 or 3 or as much) on crystal Report, so in the above mentioned query I'm simply showing the 5 number of records (which is a dummy value) but how sql server will determine that how many numbers of new records have been inserted?Did you get my idea? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-17 : 13:28:17
|
For that you need a timestamp column------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-03-17 : 19:43:12
|
A timestamp might do it but to make multi-user correct you'd need the client transaction to insert into a separate table containing a "batch ID" identity and an optional timestamp. You then create one of those and store that ID against the records you insert into the table referred to in your original question.Depending on if you want the most recent insert statement or most recent start of transaction, you'll need to get the highest ID or the highest ID with the latest date from the batch ID table.Make sense? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-18 : 01:49:29
|
Use ROWCOUNT to get number of rows inserted... based on this count you can display those records on grid...insert into tableNameSELECT * FROM tableName WHERE <conditions> SELECT @@ROWCOUNTBut this method is depends on type of insert...--Chandu |
|
|
Abid
110 Posts |
Posted - 2013-03-18 : 11:48:50
|
@ LozTInSpaceHi. the timestamp is absolutely new thing to me. Totally Unfamilier to this. Please guide me further that i could overcome with my problem. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-18 : 12:33:47
|
quote: Originally posted by Abid @ LozTInSpaceHi. the timestamp is absolutely new thing to me. Totally Unfamilier to this. Please guide me further that i could overcome with my problem.
you could just add a datetime column defaulted to getdate() to get date value auto populated.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-03-18 : 19:55:04
|
@abid - I mean a date/time not timestamp. Visakh16 is correct that a datetime on your rows could work provided you don't need to worry about two users hitting the database at the same time. That might be ok if this is a schedule batch run of some sort.What I meant was something like this (not in front of SQL Server so syntax is not likely to be correct):create table batch(id int identity(1,1) primary key,batchtime datetime not null default getdate())insert into batch values () -- Can't remember how you insert nothing into a table.insert into yourTable......values (@@scope_identity)that will group your inserts |
|
|
Paramasivan B
Starting Member
5 Posts |
Posted - 2013-03-19 : 00:08:23
|
While you creating a table, add one column like "ID" with IDENTITY.IDENTITY is making rowcount automatically. first inserted data id is 12nd inserted id value is 2 up to end if you need last column means YOU CAN TAKE MAX(ID)EXCREATE TABLE LAST_RECORD(ID INT IDENTITY,SNO INT) INSERT INTO LAST_RECORD VALUES(1)INSERT INTO LAST_RECORD VALUES(3)INSERT INTO LAST_RECORD VALUES(5)Last inserted value is 5. if you need that below query is useful for thisSELECT * FROM LAST_RECORD where SNO=(SELECT MAX(ID) FROM LAST_RECORD)Paramasivan B |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-19 : 01:02:37
|
quote: Originally posted by Paramasivan B While you creating a table, add one column like "ID" with IDENTITY.IDENTITY is making rowcount automatically. first inserted data id is 12nd inserted id value is 2 up to end if you need last column means YOU CAN TAKE MAX(ID)EXCREATE TABLE LAST_RECORD(ID INT IDENTITY,SNO INT) INSERT INTO LAST_RECORD VALUES(1)INSERT INTO LAST_RECORD VALUES(3)INSERT INTO LAST_RECORD VALUES(5)Last inserted value is 5. if you need that below query is useful for thisSELECT * FROM LAST_RECORD where SNO=(SELECT MAX(ID) FROM LAST_RECORD)Paramasivan B
ID columns will help you to identify last inserted records only if you store MAX(ID) somewhere after each table population.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Abid
110 Posts |
Posted - 2013-04-06 : 05:52:52
|
quote: Originally posted by visakh16
quote: Originally posted by Abid
quote: do you've a datetime field or auto increment id field in your table? is values stored in same or diffrent fields within sql server?
Yes i have Primary Key which is set to Auto_Increment and data is storing in the main (same) table.
then use SCOPE_IDENTITY to get inserted id value. In case of multiple value inserts use OUTPUT clause to get values from INSERTED table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I tried to search on OUTPUT clause, but i didn't understand. |
|
|
Abid
110 Posts |
Posted - 2013-04-06 : 06:00:21
|
@ Params, @ VisakhI'm using these two 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)" Please tell me which one is correct. |
|
|
Abid
110 Posts |
Posted - 2013-04-06 : 06:02:41
|
quote: Originally posted by visakh16
quote: Originally posted by Abid @ LozTInSpaceHi. the timestamp is absolutely new thing to me. Totally Unfamilier to this. Please guide me further that i could overcome with my problem.
you could just add a datetime column defaulted to getdate() to get date value auto populated.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
How to use GetDate method? |
|
|
|