Ron,

I am a begining HTML editor with some experience in Java, JavaScript, VB, VBScript, and C++. My question is this how do I go about dynamically linking an option list to a SQL database? Actually if you have any refrence material that would be great. BTW I love O'Reilly Press material, I already have four of your books on various aspects of web programming, so if there are any O'Reilly books on this that would be my first choice. If not then where should I be looking?

Thanks,
William Reaugh

Hi William,

Your question is a really good one, and one that's becoming increasingly common as the web becomes a medium for database publishing -- that is, for displaying recordsets, the results of database queries, etc.

There are any number of ways to link an HTML form to a SQL database. Since my own expertise as a developer is in Microsoft products and technologies, the solution that I'd propose necessarily relies heavily on the technologies that I'm familiar with and have actually worked with.

On the client, the most portable way of displaying the option list is simply to use the HTML <SELECT> tag; this will work on any browser that supports forms. When the user clicks the Submit button, the selection is sent to the URL specified by the <FORM> tag's ACTION attribute. The O'Reilly book that would most help you with this front-end design is HTML: The Definitive Guide and (if you want to include any client-side script) JavaScript: The Definitive Guide.

At the server end, the server receives the form information in the body of the HTTP request. If that server is Microsoft Internet Information Server or Personal Web Server, then I'd use Active Server Pages to retrieve the user selection, link to the database, and return the result set. (Active Server Pages is the IIS object model; it represents the objects IIS exposes that allow you to built custom extensions that supplement and enhance the basic functionality of the web server.) We've recently released ASP in a Nutshell, which is a concise reference guide to developing ASP applications. If you don't have IIS or PWS, then CGI or Win-CGI is still a useful alternative. CGI application development is covered in CGI Programming with Perl.

The remaining question is how to connect to the SQL database. Here, you really have to make two different choices, one obvious and the not so obvious. The first and obvious choice is the data access technology to use. For its elegance and relative simplicity, assuming that you're developing your server application on Windows, I'd select ADO (or ActiveX Data Objects), which is Microsoft's high-level object model for universal data access. We'll be publishing a book later this summer, Mastering ActiveX Data Objects, that shows how to use ADO for both web and non-web development.

The second and not obvious choice is the technology that you want to use to wrap the data access. It's not obvious because many web developers don't ask the question in the first place. As a result, ASP and probably VBScript, in the case of our application, would be used as a wrapper for the data access. A problem here, though, is that access to all ADO objects that occurs from script is necessarily late bound. That means that it's expensive and offers relatively poor performance. In contrast, by developing an ASP component using either Visual C++ or Visual Basic, you can use early binding to resolve ADO object references. An added advantage is that, if they're properly designed, ASP components are reusable. Details about creating ASP components are available in our forthcoming (April 1999) book, Developing ASP Components.

--Ron

Return to: Ron's VB Forum