ASP If Statement

dalezjc · December 19th, 2008, 01:35 PM

I have the following ASP code that works fine, up to the IF statement. I'm trying to display an image based on the result of the MachineName variable.

I'm getting "incorrect syntax errors" where the IF statement begins.

Code:

set rsqdb = conn.Execute ("Select MachineName, KPName, Severity, " _
& " EventMSG + '; ' + AgentMsgShort as EventMessages, DateAdd(ss, LastOccurTime-3600, '1969-12-31T18:00:00.000') as OccurTime " _
& " From Event, EventDetail " _
& " Where Severity < 6 " _
& " AND Status = 0 " _
& " AND EventDetail.EventID = Event.EventID " _
& " AND AgentMsgShort <>  'NULL' " _
& " AND CAST(FLOOR(CAST(DATEADD(S,LastOccurTime, '1969-12-31T18:00:00.000') AS FLOAT)) AS DATETIME) >= (getdate() -1 ) " _
& " AND EventMSG NOT LIKE '%Work Queue Length%' " _
& " AND EventMSG NOT LIKE '%DRA Outbound%' " _
& " AND EventMSG NOT LIKE '%Report Creation Failed%' " _
& " AND EventMSG NOT LIKE '%NCOGateway%' " _
& " AND EventMSG NOT LIKE '%Diagnostic Console%' " _
& " AND EventMSG NOT LIKE '%Pdsk%' " _
& " AND EventMSG NOT LIKE '%EventLog does not exist%' " _
& " Order By OccurTime DESC " _
 
&" If Machinename = 'enpefr' then " _
&" <img src="map10.jpg"> " _
&" end if ")

Any help is appreciated!

Dale

alliancejhall · December 19th, 2008, 08:55 PM

Is that if part of your SQL query? I don't think it's supposed to be but that's what you are doing? your adding it to the end of your query. Try this:

Code:

<%
set rsqdb = conn.Execute ("Select MachineName, KPName, Severity, " _
& " EventMSG + '; ' + AgentMsgShort as EventMessages, DateAdd(ss, LastOccurTime-3600, '1969-12-31T18:00:00.000') as OccurTime " _
& " From Event, EventDetail " _
& " Where Severity < 6 " _
& " AND Status = 0 " _
& " AND EventDetail.EventID = Event.EventID " _
& " AND AgentMsgShort <>  'NULL' " _
& " AND CAST(FLOOR(CAST(DATEADD(S,LastOccurTime, '1969-12-31T18:00:00.000') AS FLOAT)) AS DATETIME) >= (getdate() -1 ) " _
& " AND EventMSG NOT LIKE '%Work Queue Length%' " _
& " AND EventMSG NOT LIKE '%DRA Outbound%' " _
& " AND EventMSG NOT LIKE '%Report Creation Failed%' " _
& " AND EventMSG NOT LIKE '%NCOGateway%' " _
& " AND EventMSG NOT LIKE '%Diagnostic Console%' " _
& " AND EventMSG NOT LIKE '%Pdsk%' " _
& " AND EventMSG NOT LIKE '%EventLog does not exist%' " _
& " Order By OccurTime DESC ")
 
If rsqdb("Machinename").ToString = "enpefr" then %>
<img src="map10.jpg">
<% end if %>

I'm a .net coder so forgive me if I used incorrect syntax but you should get the Idea....

Old Pedant · December 19th, 2008, 09:59 PM

One thing in that bothers me:

You are doing:

AND CAST(FLOOR(CAST(DATEADD(S,LastOccurTime, '1969-12-31T18:00:00.000') AS FLOAT)) AS DATETIME) >= (getdate() -1 )

Sor from this I gather that your LastOccurTime values are stored in the DB as seconds since 1/1/1970 GMT. Standard Unix/Linux time, in other words.

I think I'd rather see you do that this way:

AND CONVERT(DATETIME, CONVERT(VARCHAR,DATEADD(s,LastOccurTime,'12/31/1969 18:00'),112),112) >= getDate()-1

Though either way should work just fine.

*EXCEPT*

Except the bigger problem is the meaning of getDate() -1. That does *NOT* mean "yesterday at midnight". That means "exactly 24 hours ago".

Since you are going to all the work to strip the time off of the date+time, you can be assured that your result will *NEVER* be equal to getDate()-1 except at exactly midnight. Is that what you intended?

dalezjc · December 22nd, 2008, 02:28 PM

Quote:

Originally Posted by alliancejhall

Is that if part of your SQL query? I don't think it's supposed to be but that's what you are doing? your adding it to the end of your query. Try this:

Code:

If rsqdb("Machinename").ToString = "enpefr" then %>
<img src="map10.jpg">
<% end if %>

I'm a .net coder so forgive me if I used incorrect syntax but you should get the Idea....

Thanks for the suggestion, but I'm getting the following error now:

Object doesn't support this property or method: 'ToString'

alliancejhall · December 22nd, 2008, 02:29 PM

...Drop the .ToString from rsqdb("MachineName")

itHighway · December 27th, 2008, 06:50 AM

Use Cstr() to convert "MachineName" value into string.

Quote:

If CStr(rsqdb("Machinename")) = "enpefr" then %>
<img src="map10.jpg">
<% end if %>

Old Pedant · December 28th, 2008, 02:06 AM

Pardon me, but that's just silly.

What kind of field could rsqdb("Machinename") be *EXCEPT* a string????

It's not going to hurt to use CSTR( ), but it will be doing exactly nothing. Except a very minor wasting of time. (Nanoseconds.)

itHighway · December 28th, 2008, 07:21 AM

Quote:

Originally Posted by Old Pedant

Pardon me, but that's just silly.

What kind of field could rsqdb("Machinename") be *EXCEPT* a string????

It's not going to hurt to use CSTR( ), but it will be doing exactly nothing. Except a very minor wasting of time. (Nanoseconds.)

:) I do not know what data type "MachineName" expects. The only reason I recommended CStr() is that, in original post "alliancejhall" is trying to convert rsqdb("Machinename") data type string.

Old Pedant · December 28th, 2008, 05:17 PM

The reason I assume it must be a string is because of the value that it is being compared to:

Code:

     If rsqdb("Machinename") = "enpefr" then

enpefr couldn't be anything but a text field in a database, so ADO (and VBS) will have already converted it to a string.

The original code did *NOT* use ToString( ).

Jason ("Alliance") added that because he is used to ADO.NET and such classes as the SQLDataReader, where all data fields are just converted to System.Object (that standard basic root class in the .NET framework) and so you *do* need to cast the Object to the correct actual data type. In VB.Net, you can do that via *either*

Code:

    MyDataReader(0).ToString( )
or
    CStr(MyDataReader(0))
or even
    String.Format("{0}", MyDataReader(0))

Anyway, the ToString( ) was bogus all along, just Jason/Alliance trying to use VB.NET code instead of VBS. That's all.

Again, your answer's not wrong, in the sense that CSTR() can't hurt. Just unnecessary. ADO and VBS do a pretty good job of converting SQL data types to the correct corresponding VBScript data types. The exception is the SQL DECIMAL data type, which is converted to the VARIANT vtDecimal type, which VBScript can *NOT* handle. So if you intend to do anything other than Response.Write such a type you indeed need to convert it to DOUBLE using CDBL( ). And now we are into exotica.

dalezjc · December 31st, 2008, 12:00 PM

Thanks for all the help guys. I do appreciate it.