Classic ASP (older site maintenace).

Lately I’ve been charged with adding some functionality to an older internal site done with good all Classic ASP and Java Script.

What a terrible mess. I’m fortunate to have learned to hook up Visual Studio to the code so I can step through the code and find out what makes it tick.

I has been a daunting task changing anything without breaking something else. I’m writing this in the middle of a break from the madness (I exaggerate of course), just so I’ll not run the hall way screaming!

In any case, Option Strict was not turned on, so there are hundreds of variables that are not dimmed. Some are mistyped (typos not the type kind) and forms are well.. malformed!

I hope I never do this to the next guy coming behind me!

BTW, if you are good programmer and you love coding don’t ever forget what it was like when you become a manager and start thinking that everything is easy!

Debugging Classic ASP

How wonderful it would be if we only worked on the latest and greatest! But Alas! That is not meant to be. I’ve been using VS2003 to debug Classic ASP web sites we still support. The ability to insert breakpoints and step through the code has been great. Recently I decided to use VS2010 to accomplish the same thing and here is what I’ve found out so far. I complicated my life a little bit more 🙂 Looking around the web I found various articles that discussed the steps to accomplish said debugging. The first thing you need to do is make sure your classic ASP site is set as the default web site in your local IIS. The built in web server will not work with classic ASP sites. Setting it up the classic ASP site as the default website was kind of odd for me. Since that’s not how we did it when using VS2003. We would have a normal local website and just open it using VS2003. Well it was not that easy, you would create a new project add all the folders and files to it and then run it. That has not changed, it’s just that now the website you are debugging is at the top of the hierarchy in your local IIS. It is also important that you click on your Configuration button for the site: Once you’re there, click on the Debugging tab and check Enable ASP server-side script debugging as well as client-side script if you need to. Make sure you apply, click Ok on the previous screen and you’re site is ready to be debugged. But if you hit the start debugging button or press F5, you’ll get all sort of nasty errors. What you have to do is view in browser first. That’s Ctrl+Shift+W or click the View in Browser icon: You can get to Ctrl+Shift+W through the keyboard, the icon or under the File -> View In Browser menu item. This will start your website on your browser, running on your local IIS. You are of course not done yet. You must attach to the process. And you do that by clicking on the menu item Debug -> Attach to Process, or menu item Tools -> Attach to Process or keyboard combination of Ctrl+Alt+P. There was a write up back in 2006 for VS2005 here: Attaching to Process It even has some code to create a macro to bypass this Ctrl+Umpteen+More madness! Finally stepping through code in all it’s glory in Classic ASP: Classic ASP lives on!

NOT IN Vs. NOT EXISTS

For some time, I have been pondering the use of NOT EXISTS in my subquery statements. A friend of mine guided me towards this method of excluding certain records quite a while ago. So I have been using NOT EXISTS but did not quite fully understand the difference between NOT IN and NOT EXISTS. In my particular situation a query with a four table join needed some optimizing plus certain records excluded from the query. I played around a bit and found that using NOT EXISTS produced a substantial difference in performance. Using NOT IN on the where clause of the query took in this case 9 minutes and 35 seconds. Using NOT EXISTS produced the results in exactly 688 milliseconds. This is a monumental difference. I also got a very clear understanding of what’s going on when I came across this post: NOT EXISTS vs NOT IN. While the post ascertains that in certain circumstances, there is no difference between the two, the best thing is to test both. I will forever keep this in mind. Thanks to my colleague Bill, to Gail from SQL in the Wild and a mention to the O’Reilly book Oracle Performance Tuning which also directs you in this path. The book is authored by Mark Gurry and Peter Corrigan. So this is what we’re talking about: Who wins this match? ——————————————————————– Select [columns] From [table1] Join [table2] on [table1.column] = [table2.column] Join [table3] on [table1.column] = [table3.column] Join [table4] on [table1.column] = [table4.column] Where condition1 = ‘x’ And table1.column NOT IN ( Select [column] From [table1] tableAlias Where tableAlias.column = ‘xvalue’) That took a whopping 9:35 minutes to complete. Whereas the query below, took all of 688 milliseconds! ———————————————————————- Select [columns] From [table1] Join [table2] on [table1.column] = [table2.column] Join [table3] on [table1.column] = [table3.column] Join [table4] on [table1.column] = [table4.column] Where condition1 = ‘x’ And NOT EXISTS ( Select ‘xvar’ From [table1] tableAlias Where tableAlias.column = ‘xvalue’ And tableAlias.column = table1.column) I think in this case, we know who the winner is!

How would you improve this?

The code below is what is mostly generated by Iron Speed code designer. I usually do most of the look up information linking on the database using foreign key links.

The look up table has three columns. A system id or code, a meta_type for categories and a description.

The filtering category is placed at the “somevalue” place holder. This whole thing is inserted into the section where the function affects the rows in question.

Some databases have multiple look up tables I use mostly one with categories to filtered for each look up.

Lookup table design.

#Region “Code Customization”

”’
”’ Override the PopulateSYS_IDDropDownList function to filter contents of Dropdown list.
”’
Protected Overrides Sub PopulateCODE_SYSIDDropDownList( _
ByVal selectedValue As String, _
ByVal maxItems As Integer)

‘ Set up the WHERE clause.
Dim wc As WhereClause = New WhereClause
wc.iAND(CODE_TABLETable.META_TYPE, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, “somevalue“)

Dim orderBy As OrderBy = New OrderBy(False, True)
orderBy.Add(CODE_TABLETable.SYS_ID, BaseClasses.Data.OrderByItem.OrderDir.Asc)

Me.CODE_SYSID.Items.Clear()
Dim itemValue As CODE_TABLERecord
For Each itemValue In CODE_TABLETable.GetRecords(wc, orderBy, 0, maxItems)

‘ Create the item and add to the list.
Dim cvalue As String = itemValue.SYS_ID.ToString()
Dim fvalue As String = itemValue.Format(CODE_TABLETable.DESCRIPTION)
Dim item As ListItem = New ListItem(fvalue, cvalue)
Me.CODE_SYSID.Items.Add(item)
Next

‘ Set up the selected item.
If Not selectedValue Is Nothing AndAlso _
selectedValue.Trim “” AndAlso _
Not SetSelectedValue(Me.CODE_SYSID, selectedValue) Then
Dim fvalue As String = OSERVICES_PROVIDEDTable.CODE_SYSID.Format(selectedValue)
Dim item As ListItem = New ListItem(fvalue, selectedValue)
item.Selected = true
Me.CODE_SYSID.Items.Insert(0, item)
End If

‘ Add the Please Select item.
Me.CODE_SYSID.Items.Insert(0, New ListItem(MiscUtils.GetValueFromResourceFile(“Txt:PleaseSelect”, “Application”), “–PLEASE_SELECT–“))
End Sub

#End Region

Pinging your way to productivity

Recently I had a bit of trouble connecting to a database from a web server. All parties involved had done their part as far as opening ports, database clients were in place (Oracle) and it seemed that we were good to go.

Using old and tried tools like IpConfig, Tracert, Ping and PathPing came to the rescue. While these are not programming tools per se, they are often used by System Admins and Network Engineers, it does not hurt to know their use.

So on this little discovery I found out that the IP address of the database we were trying to connect to was completely wrong. Once I corrected that, other issues had to be solved.

Now that we knew we had the righ IP Address, we were still not connecting. After delivering some information obtained via Tracert, we were able to determine what the problem was. The ports on the web server had been opened on the wrong subnet IP Address.

You may have your code ready to go but simple old tools like the ones above saved the day.

If you need  a refresher on what this tools do look up this link: Trace Route