Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    executing simple .js file

    Hi all,

    I'm a beginner in javascript
    so, i was trying to execute a single simple .js file that should retrieve a value from a sql server db and display it in a popup window like the following:

    var Statement = "SELECT coalesce(TotalWork,0) AS TotalWork FROM dual as x left outer join (SELECT 1 as Y,CONVERT(INT, SUM(MSP_TimesheetActual.ActualWorkBillable)) AS TotalWork FROM MSP_EpmResource LEFT OUTER JOIN MSP_TimesheetResource INNER JOIN MSP_TimesheetActual ON MSP_TimesheetResource.ResourceNameUID = MSP_TimesheetActual.LastChangedResourceNameUID ON MSP_EpmResource.ResourceUID = MSP_TimesheetResource.ResourceUID LEFT OUTER JOIN MSP_TimesheetPeriod INNER JOIN MSP_Timesheet ON MSP_TimesheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID ON MSP_TimesheetResource.ResourceNameUID = MSP_Timesheet.OwnerResourceNameUID WHERE (MSP_EpmResource.ResourceNTAccount = 'INTERCOM\%username%') AND (MSP_TimesheetActual.TimeByDay BETWEEN MSP_TimesheetPeriod.StartDate AND MSP_TimesheetPeriod.EndDate) GROUP BY MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate HAVING (MSP_TimesheetPeriod.StartDate <= DATEADD(d, - 7, GETDATE())) AND (MSP_TimesheetPeriod.EndDate >= DATEADD(d, - 7, GETDATE()))) as y on x.x=y.Y";
    var Connection = new ActiveXObject('ADODB.Connection');
    var Recordset = new ActiveXObject('ADODB.Recordset');
    var Shell = new ActiveXObject('WScript.Shell');

    Connection.Open('Data Source={servername};Initial Catalog={database name};User ID={username};Password={password};Provider=SQLOLEDB');
    Recordset.Open(Statement, Connection);
    Recordset.MoveFirst();

    while (!Recordset.EOF)
    {
    Shell.Popup(Recordset('TotalWork'), 0, 'Message', 48);
    Recordset.MoveNext();
    }

    Recordset.Close();
    Connection.Close();

    but for some reasons it returns 0
    and while i'm trying to run the query on sql server it returns the correct value
    does anybody have an idea about where the error may be ??

    thanks in advance

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Have you tested with some simpler SQL statement, to find out if the problem is in the query or elsewhere?

    Maybe try something as simple as
    Code:
    var Statement = "SELECT COUNT(*) FROM dual";
    OH! Wait! I might know the answer!

    Code:
    Recordset('TotalWork')
    is an *OBJECT*!!!! It is an ADODB.Field object, with various properties, including Name and Type and, what you want, Value.

    Normally, we expect to get the default property, which is Value, but it's possible that in the Popup usage the ActiveX default property is not being invoked.

    Try doing
    Code:
        var fld = Recordset("TotalWork");
        Shell.Popup( fld.Name + ": " + fld.Value, 0, 'Message', 48);
    That's just a guess. I've never used Shell.Popup, but I've seen this problem with JS coding and ADODB.Field in other cases.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Dear Old Pedant,

    i've tried to run this script:
    var Statement = "select ResourceNTAccount from MSP_EpmResource";
    var Connection = new ActiveXObject('ADODB.Connection');
    var Recordset = new ActiveXObject('ADODB.Recordset');
    var Shell = new ActiveXObject('WScript.Shell');

    Connection.Open('Data Source=projectserver;Initial Catalog= PWA_ProjectServer_Reporting;User ID=sa;Password=intercom.com.eg;Provider=SQLOLEDB');

    Recordset.Open(Statement, Connection);
    Recordset.MoveFirst();

    while (!Recordset.EOF)
    {
    Shell.Popup(Recordset('ResourceNTAccount'));
    Recordset.MoveNext();
    }

    Recordset.Close();
    Connection.Close();

    and it works perfectly both in the server and the remote computer even in the client computer

    but when i change the query to something like:
    select ResourceNTAccount from MSP_EpmResource where ResourceNTAccount='someuser'

    the following error ocurred:

    Line:10
    Char:1
    Error: Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record.
    Code: 800A0BCD
    Source: ADODB.Recordset

    i'm sure that the user exist and i've tried to run the query on the sql server and it runs successfully

    have you any suggestions ??

    thanks alot for your help

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Well, it seems silly, but JUST IN CASE, you might do
    Code:
    var Statement = "select ResourceNTAccount from MSP_EpmResource where ResourceNTAccount='someuser'"
    Shell.Popup( Statement );
    ...
    To make sure that indeed the statement being used is the one you expect it to be.

    By the by, Recordset.MoveFirst(); is pointless. If the recordset opened successfully, at all, it *WILL* be already at the first record. If it didn't open successfully, you'll get the error message you noted.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    devmostafa (12-22-2010)

  • #5
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Dear Old Pedant,

    thanks alot for your efforts, my problem has been finally solved

    but it looks like i still need your help for one last time
    i need to run this query again after 5 mins

    could you please help me with the syntax to add a timer to my .js file

    thanks in advance

  • #6
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    any suggestions ??

  • #7
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    17,918
    Thanks
    203
    Thanked 2,531 Times in 2,509 Posts
    Quote Originally Posted by devmostafa View Post
    any suggestions ??
    Give Old Pedant a chance! He has to sleep sometimes, you know. It is bad form to bump your thread after only three hours - the forum rules say you should wait 2-3 days.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Quote Originally Posted by Philip M View Post
    Give Old Pedant a chance! He has to sleep sometimes, you know.
    LOL! Too true! Especially when devmostafa's question and bump came at 2:40 AM and 5:39 AM, my time. I *guarantee* you I was sleeping then.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,171
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Since you are using Windows Script Host to run this, just use the Sleep that WSH provides.
    Code:
    WScript.Sleep( 5 * 60 * 1000 ); // sleep time is in milliseconds
    http://msdn.microsoft.com/en-us/libr...fd(VS.85).aspx
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks alot guys for your help,

    now i have only ne request is anyway to use WSH to loggoff computer

    thanks in advance and please do not forget that differnet countries always have different times

    my time now is 2:58 PM


  •  

    Posting Permissions

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