...

View Full Version : executing simple .js file



devmostafa
12-20-2010, 01:39 PM
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

Old Pedant
12-20-2010, 09:26 PM
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

var Statement = "SELECT COUNT(*) FROM dual";


OH! Wait! I might know the answer!



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


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.

devmostafa
12-21-2010, 10:44 AM
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

Old Pedant
12-21-2010, 07:32 PM
Well, it seems silly, but JUST IN CASE, you might do


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.

devmostafa
12-23-2010, 10:40 AM
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 :)

devmostafa
12-23-2010, 01:39 PM
any suggestions ??

Philip M
12-23-2010, 01:46 PM
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.

Old Pedant
12-23-2010, 07:36 PM
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.

Old Pedant
12-23-2010, 07:40 PM
Since you are using Windows Script Host to run this, just use the Sleep that WSH provides.


WScript.Sleep( 5 * 60 * 1000 ); // sleep time is in milliseconds

http://msdn.microsoft.com/en-us/library/6t81adfd(VS.85).aspx

devmostafa
12-26-2010, 12:56 PM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum