MSSQL Trigger (i think) Question

[k-strider]

Ok I am in no way even an armature in SQL..... but this is what I want to do... we are trying to log usage of staff laptops, so I adapted a script my old colleague wrote for logging users who login anywhere in the school… (this should in theory catch them too but the staff laptops aren’t named in an easy selectable manner to run a query against the existing table so I just want to put them into another table too..

 

So I successfully changed the script and it works… however I had a problem with the Date (so does the original but as its for tracking students in IT rooms its ok), I guess some of the laptops must have American regional settings as it doesn’t insert a row when I include the date field (with a couple of exceptions)… so I was wondering rather than the script inserting the date can I get the server to automatically append the date to the row when it detects an insert? (I could also get rid of my Time Colum if I get the date field to work correctly…)

 

Original snippet (only caught some users because of the date field)

 

s = Wshnetwork.computername
nm = wshnetwork.username

theTime = FormatDateTime (now(),4)
theDate = Date()

s_sql = "INSERT INTO Teach_Sign (SignUser, SignComputer, SignTime,  SignDate) VALUES ('" & nm & "','" & s & "','" & theTime & "','" & theDate & "')"
m_rs.open s_SQL, m_cn, adOpenStatic 

 

 

 

My Script Snipit that works… but cause I removed the date field they all appear as 01/01/1900 00:00

 

s_sql = "INSERT INTO Teach_Sign (SignUser, SignComputer, SignTime) VALUES ('" & nm & "','" & s & "','" & theTime &  "')"

 

Like I said I would prefer the server side to append the date field but if you know how to make the VBS force the date to UK easily too that would do!!

 

Thanks Gordy.

[webman]

IIRC, Date values have to be encased on # symbols, Eg. ..."#" & theDate & "#"...

[Iain]

When you create the table, if you set the default value for the SignDate field as the MS SQL function GetDate(), the server will automatically insert the current date and time when the record is created (if you do not specify a value in the INSERT query).....

 

MS SQL:

CREATE TABLE [Teach_Sign] ([signUser] varchar(25) NOT NULL, [signComputer] varchar(25) NOT NULL, [signDate] datetime DEFAULT GetDate());

[Obviously you need to define any primary keys, relationships etc.]

 

Then all you should need to do is insert the username and computername (using your origional code)......

s = wshnetwork.computername
nm = wshnetwork.username

s_sql = "INSERT INTO Teach_Sign (SignUser, SignComputer) VALUES ('" & nm & "','" & s & "')"
m_rs.open s_SQL, m_cn, adOpenStatic

 

Hope this is of some help,

 

Iain.

[k-strider]

Thanks Iain

 

the Default GetDate() gave me the clue.. so I found the Colum in the SQL database and put this in as the default value removed the date and time from the insert and the time Colum for the DB and it now works a treat !!!

 

Thanks...