CHECK IF USER ALREADY LOGGED IN

[Simon Gare]

Hi all,

below is an insert statement on an asp page that stores the date and time
that a driver logged on, what I need is to check that they are now already
logged on fields are

SQL Server 2000

ID int
DRIVER_NO int
ON_DATE datetime
OFF_DATE datetime
ON_NOW nvarchar
SESSION_ID int

The ON_NOW column reads on or off depending whether the driver logged out or
not, if they havent we need to close the previous logon session and mark it
with 'off' and enter a date time into OFF_DATE column.



sql = "INSERT INTO logon (DRIVER_NO, ON_NOW, SESSION_ID" & _
") VALUES (" & _
"'" & username & "', 'on', '" & Session.SessionID & "')"


racking my brains with this one for days any help would be appreciated.

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

 

[Bob Barrows [MVP]]

Hi all,

below is an insert statement on an asp page that stores the date and
time that a driver logged on, what I need is to check that they are
now already logged on fields are

SQL Server 2000

ID int
DRIVER_NO int
ON_DATE datetime
OFF_DATE datetime
ON_NOW nvarchar
SESSION_ID int

The ON_NOW column reads on or off depending whether the driver logged
out or not, if they havent we need to close the previous logon
session and mark it with 'off' and enter a date time into OFF_DATE
column.



sql = "INSERT INTO logon (DRIVER_NO, ON_NOW, SESSION_ID" & _
") VALUES (" & _
"'" & username & "', 'on', '" & Session.SessionID & "')"


racking my brains with this one for days any help would be
appreciated.

I don't understand what the problem is. You have a vbscript statement that
appears as if it will generate a valid sql insert statement (I assume you've
written it to Response and verified that the statement being generated is
valid and runs in QA). What do you need help with?

 

[Simon Gare]

Hi Bob,

the insert statement works, but if the driver is already logged in I need
some way of closing the last session stored in the table, some kind of IF
statement attached to the below. As per the table below the data would read

574 16 13/04/2007 13:03:52 <NULL> on 938471687

If the driver doesn't log off, when he logs on again it would create another
entry

575 16 13/04/2007 13:15:03 <NULL> on 938471958

I need a way of checking if driver username status from previous logon =
'on' if so then update row i.e.

574 16 13/04/2007 13:03:52 13/04/2007 13:15:02 off 938471687

then create new entry.

Hope that explains it better.

Regards
Simon

 

[Bob Barrows [MVP]]

So create a stored procedure that accepts the username and session id.

CREATE PROCEDURE LogDriverOn (
@driver int,
@session int) AS
DECLARE @now datetime
SET @now=GETDATE()
UPDATE logon
SET ON_NOW='off',
OFF_DATE = @now
WHERE DRIVER_NO = @user AND
ON_NOW = 'on'
INSERT INTO logon (DRIVER_NO, ON_NOW, SESSION_ID)
VALUES (@user,'on',@now,@session)

Then in ASP, simply call it like this:

conn.LogDriverOn username, Session.SessionID

Bob Barrows

 

[Bob Barrows [MVP]]

Oh, geez, I lost track of my variables. See edits inline:

So create a stored procedure that accepts the username and session id.

CREATE PROCEDURE LogDriverOn (
@driver int,
@session int) AS
DECLARE @now datetime
SET @now=GETDATE()
UPDATE logon
SET ON_NOW='off',
OFF_DATE = @now
WHERE DRIVER_NO = @user AND

WHERE DRIVER_NO = @driver AND

ON_NOW = 'on'
INSERT INTO logon (DRIVER_NO, ON_NOW, SESSION_ID)
VALUES (@user,'on',@now,@session)

VALUES (@driver ,'on',@now,@session)