+ Post New Thread
Results 1 to 4 of 4
Coding Thread, MSSQL Trigger (i think) Question in Coding and Web Development; Ok I am in no way even an armature in SQL..... but this is what I want to do... we ...
  1. #1
    k-strider's Avatar
    Join Date
    Oct 2006
    Location
    Gloucester
    Posts
    358
    Thank Post
    7
    Thanked 40 Times in 30 Posts
    Rep Power
    24

    MSSQL Trigger (i think) Question

    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)

    Code:
    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

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

  2. #2

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,414
    Thank Post
    642
    Thanked 964 Times in 664 Posts
    Blog Entries
    2
    Rep Power
    327

    Re: MSSQL Trigger (i think) Question

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

  3. #3
    Iain's Avatar
    Join Date
    Oct 2006
    Location
    Warwickshire
    Posts
    190
    Thank Post
    28
    Thanked 94 Times in 54 Posts
    Rep Power
    32

    Re: MSSQL Trigger (i think) Question

    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:
    Code:
    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)......
    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.

  4. #4
    k-strider's Avatar
    Join Date
    Oct 2006
    Location
    Gloucester
    Posts
    358
    Thank Post
    7
    Thanked 40 Times in 30 Posts
    Rep Power
    24

    Re: MSSQL Trigger (i think) Question

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



SHARE:
+ Post New Thread

Similar Threads

  1. SIMS on MSSQL 2005
    By AlexB in forum MIS Systems
    Replies: 9
    Last Post: 7th November 2007, 10:20 AM
  2. Open Source CMS that can use MSSQL
    By ICTNUT in forum Windows
    Replies: 4
    Last Post: 29th March 2007, 04:08 PM
  3. WSUS & Sophus - MSSQL ??
    By tech_master in forum Windows
    Replies: 3
    Last Post: 18th January 2007, 07:22 PM
  4. MSSQL Trigger (i think) Question
    By k-strider in forum Coding
    Replies: 0
    Last Post: 27th November 2006, 12:45 PM
  5. Autocreate AD accounts from MSSQL
    By CyberNerd in forum Coding
    Replies: 9
    Last Post: 12th July 2006, 04:00 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •