Any ASP coders out there

[timwilky]

OK, I am trying to debug an ex employees code. It turns out this guy for some reason developed a business app in ASP (Breaks all company rules as we only support PHP and JSP as scripting languages). But it is here now and I have been given the poison chalice.

I have never used ASP. and can now understand why I made this decision. The ex guy seems to have embeded asp functions within sql calls. such as :-


sqlstr = "SELECT datepart(ww, mdate) as weekno FROM tblIntranetScheduled WHERE IsRemoved=0 AND datepart(ww, mdate) between datepart(ww, '" & date() & "')-2 and datepart(ww, '" & date() & "')+4 AND IsArchived=0 GROUP BY datepart(ww, mdate)"

I think this is my first problem. As simply using weeks numbers, is failing to distinguish between this year, last year etc.





I am assuming the date() function he is using is as asp date function. Therefore does not sqlserver (Sorry I use Oracle) not have built in date functions. Am I missing something here.

I guess above, he is trying to work out some sort of week number but am now confused by his use of -2 +4 as if he is trying to define his week as today -2 to today +4.

Am I on the right track. I would have thought it would have been simpler to either use an SQL date function or to have worked out his range before putting it into the query.

Pointer/advice required. Otherwise I will simply hit this with a big hammer

[TSM]

Most sql dbs have powerfull date functions as you know, why he uses the ASP date rather than date in the sql db i dont know, if anything its more incompatable if for some reason the ASP date is not understood by the sql server. Just dump it and do it all in SQL.

yup, he's listing the week number for each row with an mdate that is within 7 days of the current date.(-2 to + 4).

It's a pretty bizarre way to do it. I can kinda guess why he's doing that but there are loads of more efficient ways to do it.

I'm not a big fan of creating sql statements in web pages. Again there're better ways of doing this. Stored procedures spring to mind as it's all compiled code, but then there are arguments against that (decentralisation of business & data logic and scalabity being the most obvious).

[timwilky]

It was not good code. The weeks he established above where then used in a where clause further down. not surprising they returned the wrong data as it failed to recognise the concept of week no within year.

I have fixed it, but by establishing the following

today = date()
dayofweek = WeekDay(today)
dayindex = dayofweek-2
if dayindex = -1 then
dayindex=6
end if
startofweek = DateAdd("d", -dayindex, today)
rangeStart=DateAdd("d", -14, startofweek)
sqlstart=MonthName(Month(rangeStart)) & " " & DatePart("d", rangeStart) & " " & DatePart("yyyy", rangeStart)
rangeEnd=DateAdd("d", 27,startofweek)
sqlend=MonthName(Month(rangeEnd)) & " " & DatePart("d", rangeEnd) & " " & DatePart("yyyy", rangeEnd)

I then use sqlstart and sqlend in the sql between clause. It does nothing but return the same week numbers from above. But cleaner and easier to understand.

However in the nest sql further down, I then add a "and mdate => sqlstart and lo and behold it now returns this years data instead of the first bits that match a simple week number where clause.

I will tell you guys something. You may cringe when I talk jsp, servlets and even php(especially with an OO implimentation) . But this asp stuff seems to be very primitive. God Even C# is an improvement.


However Kinvig can I please take you to task with

Originally Posted by kinvig

I'm not a big fan of creating sql statements in web pages. Again there're better ways of doing this. Stored procedures spring to mind as it's all compiled code, but then there are arguments against that (decentralisation of business & data logic and scalabity being the most obvious).

I fail to understand you argument against using a stored procedure. I come from an environment where you would deliberately prevent access to data and instead provide a method encapsulated within a class to retrieve the data. That way you need not know how the data is stored/constructed etc. Simply how to retrieve it. As a programmer, I would prefer the DBA has managed the data appropriately and provided methods for me to optain the correct data.

I did also work as a DBA. I would despair at programmers who thought they could design databases. Failed to normalise. build integrity constraints etc. That is the problem with low end databases. They tend to be easy to build. and therefore everyone thinks they can do it. Whereas at the high end mission critical, they are designed from the ground up. Access methods are built in. nobody works with raw data. They must use tested, documented and maintained methods