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

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"

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]" <reb01501@NOyahoo.SPAMcomwrote in message
news:%23cF5JJcfHHA.1816@TK2MSFTNGP06.phx.gbl...

Simon Gare wrote:

>
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"
>
>

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 <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]" <reb01501@NOyahoo.SPAMcomwrote in message
news:%23cF5JJcfHHA.1816@TK2MSFTNGP06.phx.gbl...

--
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.

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.