CHECK IF USER ALREADY LOGGED IN

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

Re: CHECK IF USER ALREADY LOGGED IN

Simon Gare wrote:
> 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?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: CHECK IF USER ALREADY LOGGED IN

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 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 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]" wrote in message
news:%23cF5JJcfHHA.1816@TK2MSFTNGP06.phx.gbl...
> Simon Gare wrote:
> > 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?
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: CHECK IF USER ALREADY LOGGED IN

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

Simon Gare wrote:
> 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 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 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]" wrote in message
> news:%23cF5JJcfHHA.1816@TK2MSFTNGP06.phx.gbl...
>> Simon Gare wrote:
>>> 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.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: CHECK IF USER ALREADY LOGGED IN

Oh, geez, I lost track of my variables. See edits inline:
Bob Barrows [MVP] wrote:
> 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)


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.