VBScript/ASP Classic

Question

I have a couple of questions regarding VBScript and ASP Classic:

1) What is the preferred way to access an MS SQL Server database in VBScript/ASP?

2) What are best practices in regards to separating model from view from controller?

3) Any other things I should know about either VBScript or ASP?

If you haven't noticed, I'm new at VBScript coding. I realize numbers 2 & 3 are kind of giant "black hole" questions that are overly general, so don't think that I'm expecting to learn everything there is to know about those two questions from here.

Answer 1

ADO is an excellent way to access a database in VBScript/Classic ASP.

Dim db: Set db = Server.CreateObject("ADODB.Connection")
db.Open "yourconnectionstring -> see connectionstrings.com"
Dim rs: Set rs = db.Execute("SELECT firstName from Employees")
While Not rs.EOF
    Response.Write rs("firstName")
    rs.MoveNext
Wend
rs.Close

More info here:http://www.technowledgebase.com/2007/06/12/vbscript-how-to-create-an-ado-connection-and-run-a-query/

One caveat is that if you are returning a MEMO field in a recordset, be sure you only select ONE MEMO field at a time, and make sure it is the LAST column in your query. Otherwise you will run into problems. (Reference:http://lists.evolt.org/archive/Week-of-Mon-20040329/157305.html )

Answer 2

On number 2, I think you have a few options...

1) You can use COM components developed in VB6 or the like to separate some of your business logic from your UI.

2) You can create classes in VBScript. There is no concept of inheritance and other more advanced features are missing from the implementation, but you can encapsulate logic in classes that helps reduce the spagehtti-ness of your app. Check out this: http://www.4guysfromrolla.com/webtech/092399-1.shtml

Answer 3

Echoing some ideas and adding a few of my own:

1) Best way to access the database would to abstract that away into a COM component of some sort that you access from VBScript.

2) If you really wanted to you could write the controller in VBScript and then access that in the page. It would resemble a Page Controller pattern and not a Front Controller that you would see in ASP.NET MVC or MonoRail

3) Why are you doing this to yourself? Most of the tooling required to do this kind of work isn't even available anymore.

Answer 4

@Ryan Rinaldi: I'd like to develop the site in a newer technology, but unfortunately, that's out of my hands.

Answer 5

way way back in the day when VBScript/ASP were still ok I worked in a utility company with a very mixed DB envrionment, I used to swear by this website:http://www.connectionstrings.com/

@michealpryor got it right

Answer 6

I've been stuck building on ASP, and I feel your pain.

1) The best way to query against SQL Server is with parameterized queries; this will help prevent against SQL injection attacks.

Tutorial (not my blog):
http://www.nomadpete.com/2007/03/23/classic-asp-which-is-still-alive-and-parametised-queries/

2) I haven't seen anything regarding MVC specifically geared towards ASP, but I'm definitely interested because it's something I'm having a tough time wrapping my head around. I generally try to at least contain things which are view-like and things which are controller-like in separate functions. I suppose you could possibly write code in separate files and then use server side includes to join them all back together.

3) You're probably coming from a language which has more functionality built in. At first, some things may appear to be missing, but it's often just a matter of writing a lot more lines of code than you're used to.

Answer 7

Remember to program into the language rather than program in it. Just because you're using a limited tool set doesn't mean you have to program like it's 1999.

I agree with JasonS about classes. It's true you can't do things like inheritance but you can easily fake it

Class Dog
    Private Parent

    Private Sub Class_Initialize()
        Set Parent = New Animal
    End Sub

    Public Function Walk()
        Walk = Parent.Walk
    End Function

    Public Function Bark()
        Response.Write("Woof! Woof!")
    End Function
End Class

In my projects an ASP page will have the following: INC-APP-CommonIncludes.asp - This includes stuff like my general libraries (Database Access, file functions, etc) and sets up security and includes any configuration files (like connection strings, directory locations, etc) and common classes (User, Permission, etc) and is included in every page.

Modules/ModuleName/page.vb.asp - Kind of like a code behind page. Includes page specific BO, BLL and DAL classes and sets up the data required for the page/receives submitted form data, etc

Modules/ModuleName/Display/INC-DIS-Page.asp - Displays the data set up in page.vb.asp.

Answer 8

Also for database access I have a set of functions - GetSingleRecord, GetRecordset and UpdateDatabase which has similar function to what Michael mentions above

Answer 9

I had to walk away from my PC when I saw the first answer, and am still distressed that it has been approved by so many people. It's an appalling example of the very worst kind of ASP code, the kind that would ensure your site is SQL-injectable and, if you continue using this code across the site, hackable within an inch of its life.

This is NOT the kind of code you should be giving to someone new to ASP coding as they will think it is the professional way of coding in the language!

1. NEVER reveal a connection string in your code as it contains the username and password to your database. Use a UDL file instead, or at the very least a constant that can be declared elsewhere and used across the site.

2. There is no longer any good excuse for using inline SQL for any operation in a web environment. Use a stored procedure -- the security benefits cannot be stressed enough. If you really can't do that then look at inline parameters as a second-best option... Inline SQL will leave your site wide open to SQL injection, malware injection and the rest.

3. Late declaration of variables can lead to sloppy coding. Use "option explicit" and declare variables at the top of the function. This is best practice rather than a real WTF, but it's best to start as you mean to go on.

4. No hints to the database as to what type of connection this is -- is it for reading only, or will the user be updating records? The connection can be optimised and the database can handle locking very efficiently if effectively told what to expect.

5. The database connection is not closed after use, and the recordset object isn't fully destroyed.

ASP is still a strong language, despite many folks suggesting moving to .NET -- with good coding practices an ASP site can be written that is easy to maintain, scaleable and fast, but you HAVE to make sure you use every method available to make your code efficient, you HAVE to maintain good coding practices and a little forethought. A good editor will help too, my preference being for PrimalScript which I find more helpful to an ASP coder than any of the latest MS products which seem to be very .NET-centric.

Also, where is a "MEMO" field from? Is this Access nomenclature, or maybe MySQL? I ask as such fields have been called TEXT or NTEXT fields in MS-SQL for a decade.

Answer 10

I agree with @Cirieno, that the selected answer would not be wise to use in production code, for all of the reasons he mentions. That said, if you have just a little experience, this answer is a good starting point as to the basics.

In my ASP experience, I preferred to write my database access layer using VB, compiling down to a DLL and referencing the DLL via VBScript. Tough to debug directly through ASP, but it was a nice way to encapsulate all data access code away from the ASP code.

Answer 11

AXE - Asp Xtreme Evolution is a MVC framework for ASP classic

There are some attempts at making test frameworks for asp: aspUnit is good, but no longer maintained.

I saw a sample on how to make your own one a few months back. The example used nUnit to call functions against the website for automatic testing. I think i got it off here (my line is borked so I can't check)