Add 1 to the result of this query

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

Add 1 to the result of this query

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2009-10-28 : 10:09:38

Hi,

"UPDATE tbl_Userslogged SET usr_nm = '" + UserID + "', login_tm = GETDATE() WHERE BFT_ID IN (SELECT BFT_ID FROM tbl_Userslogged) "

This is the query i have.How can i add 1 to the result of select BFT_ID from tbl_userslogged?(BFT_ID id the primary key column)

Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 10:12:39

SELECT BFT_ID+1 FROM tbl_Userslogged

Jim

Everyday I learn something that somebody else already knew

rowter
Yak Posting Veteran

76 Posts

Posted - 2009-10-28 : 10:58:42

Hi Jim,
This answered my question. But for some reason, this query is executing but not writing anything to the table.

It did write onl once when the BFT_ID = 12 it saevd the information to the table bu next time onwards,it is not writing anything.
Do you hve any idea why this is happening?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-28 : 11:10:30

Can you maybe explain what exactly you are trying o do with this UPDATE?

Some sample data and expected result would help.

rowter
Yak Posting Veteran

76 Posts

Posted - 2009-10-29 : 10:21:28

Hi,

In my winforms Application, when i login , it has to send the username, the login time to this table. This table shows all the people logged in to the application currently.

This has 3 columns BFT_ID (Primary Key), usr_nm, login_tm.
This query is failing

"UPDATE tbl_Userslogged SET usr_nm = '" + UserID + "', login_tm = GETDATE() WHERE BFT_ID IN (" _
'+ "SELECT MIN(BFT_ID) AS BFT_ID FROM tbl_Userslogged WHERE usr_nm IS NULL)"

This query is executing but it is not populating the database.(0 rows affected) I am nto sure why this is happening?
So, i tried to change the query bring the current current value of bft_ID from the table and then add 1 to it

UPDATE tbl_Userslogged SET usr_nm = '" + UserID + "', login_tm = GETDATE() WHERE BFT_ID IN (SELECT BFT_ID + 1 FROM tbl_Userslogged)

This query is executing too but not populating the databse.
If the BFT_ID is 12 , then it is populating the data for other values it is not populating the data

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-29 : 14:14:27

Is it possible that tbl_Userslogged is empty?

What does your select return?

SELECT MIN(BFT_ID) AS BFT_ID FROM tbl_Userslogged WHERE usr_nm IS NULL

rowter
Yak Posting Veteran

76 Posts

Posted - 2009-10-29 : 18:02:48

Hi Lamprey,

Rows 1 to 11 have been deleted earlier. So nothing there
Right now i have this query in my application:
UPDATE tbl_Userslogged SET usr_nm = '" + UserID + "', login_tm = GETDATE() WHERE BFT_ID IN (SELECT BFT_ID + 1 FROM tbl_Userslogged)

select * from tbl_Userslogged ---------------------- returns 2 rows
BFT_ID - 12 usr_nm - NULL login_tm - NULL
BFT_ID - 13 usr_nm - user1 login_tm - 2009-10-29 4.53.......

SELECT MIN(BFT_ID) AS BFT_ID FROM tbl_Userslogged WHERE usr_nm IS NULL returns NULL
BFT_ID - NULL

The way i want it is when a user logs in it has to go there and stay till he/she logs out.
The query i am using may be wrong. This was in the application for a long time and i am not sure if it ever worked.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources