We have now set up an ODBC Data Source to interact with a database on a Microsoft SQL Server.

You Gotta Love SQL

All the students that have a graduation date of 1999 will be deleted from the Students database.

In this instance someone had mistakenly misspelled Chuney’s name as Shotney. To correct this error an update needed to occur as evidence by the UPDATE SQL command. This only works, however, if there is only one member in the database with the last name of Johnson. If there are multiple instances of "Johnson" in the database then it would update all the first name fields of all the Johnsons. To make sure that this type of error does not occur a primary key field must be identified. A primary key field is a field that can only correspond to one record. A good primary field key to set up in the case of a student database would be the Social Security field.

The commands that control creating databases, deleting databases, deleting all the information contained in a database are also very simple SQL commands.

Ok, so we all remember the Commodore 64s and Apple IIs and whatever else we had when we were younger on which we sometimes programmed. The code looked something like this:

Well, BASIC has improved and has now become Visual. One of the three great strengths of Visual Basic is the ability to easily write programs. Well, I guess that was always a strength. Another strength of Visual Basic is the ease of connecting to databases. Access, FoxPro, SQL Server, you name it and chances are there is some way you can easily write a few lines of code to obtain data from these databases and utilize it within your programs. The two ways of connecting to databases are through the Microsoft Jet engine or through ODBC. The type of connection we will be focusing on is ODBC. Since we already have a working knowledge of SQL and we know how to set up a data source we can start writing programs with Visual Basic to access a database.

Code, Code, Code

We will assume that the SQL Server has been correctly set up and that a database has already been created for us. The database name will be "Sample" and the table name that we will be accessing will be "Students". We will also utilize the DSN that we created earlier. There is one more setting that we must work with before we begin. We must enable the DAO Object Library. This controls the database access object code that we will be using, such as workspaces and recordsets. We can accomplish this by selection Project from the menu and then references from the list. We then put a check by "Microsoft DAO 3.51 Object Library".

Dim wrkODBC As Workspace

Next we set the variable to create a workspace with the name of ODBCWorkspace. The second field in the CreateWorkspace function refers to a username and the third to a password. These are used if you want to set up a user and a password to access the workspace. (See UserName for more information). The last field refers to the type of connection we want to make, in this case ODBC.

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)

After correctly setting up the workspace we can now make the connection to the database. Again we must set up the variables to access the database.

Dim X As String
Dim StudentDatabaseConnection As Connection
Dim StudentDatabase As Recordset

Now we can set up the connection to the database which will occur within our Workspace. Here we are setting the StudentDatabaseConnection to be opened in the wrkODBC workspace. The first field refers to the fact that we are opening the database "Students". The second field refers to options that can be set up within the connection. The third field is a boolean value that is used to show whether the database should be opened as read-only or not. The last field refers to the connection. The connection here is declared by the variable X. It is the information needed to connect to the DSN (Students) we previously set up and to the database (sample) defined in the SQL Server itself.

X="ODBC;database=sample;DSN=Students;UID=sa;PWD="
Set StudentDatabaseConnection = wrkODBC.OpenConnection("Students", , False, X)

Once we have created the needed connection we can then create a variable to access the table from the database as a recordset. Using the variable that we set up as the connection we then use the function "OpenRecordset". The first field in this function refers to the table within the database we would like to open, in this case "Students". The second field refers to the fact that we wish to open this table for dynamic (or read/write) use. Other options are Read-Only and Forward-Only.

Set StudentDatabase = StudentDatabaseConnection.OpenRecordset("Students", dbOpenDynamic)

We could have set up the StudentDatabase like this as well, however many of our SQL Queries occur with the Connection, not on the Recordset.

wrkODBC.OpenConnection("Students", , False, X).OpenRecordset("Students", dbOpenDynamic)

We can now start performing SQL queries on the table within the database. We can begin with a simple insert function that will allow us to add data to the table. Currently, the fields that are contained within the table are: SSN, Firstname, Lastname, Major, GradDate. We will set up five text boxes within our Visual Basic form to enter the data. They will be referenced as txtSSN, txtFirstname, txtLastname, txtMajor, and txtGradDate. To enter the query into the database a string variable, SQLStmt is defined. The variables that are contained within our Visual Basic program must be placed into the SQLStmt string so that they can be processed in the query. To accomplish this task we first add a single quote, a double quote, and then an ampersand. We then add the variable name and then finish it with an ampersand, a double quote, and a single quote. This shows that the variable value, not the variable name is to be placed into the table. To make strings longer, such as SQL queries so they do not run off the page. We can begin the SQLStmt and then add to it with the use of SQLStmt = SQLStmt & String. In this way we can build strings, such as querys with multiple lines. We should set up a button, cmdSave that will run this function.

Dim SQLStmt As String
SQLStmt = "INSERT INTO Students "
SQLStmt = SQLStmt & "(SSN, Firstname, Lastname, Major, GradDate)"
SQLStmt = SQLStmt & "VALUES('" & txtSSN.Text & "','" & txtFirstname.Text & "','" & txtLastName.Text & "', '" & txtMajor.Text & "', '" & txtGradDate.Text & "')"

Once the statment has been formed we must run the query against the table. We accomplish this with the command:

StudentDatabaseConnection.Execute SQLStmt

Notice that it is the connection, not the recordset that executes the needed command. Now that we have information in the database we are ready to retrieve that information. Since we have already written a function to make a connection to the database if we run that function again, it will refresh the table information. We can then load the database information into the textboxes we have already set up.

txtSSN.Text = StudentDatabase.Fields("SSN")
txtFirstname.Text = StudentDatabase.Fields("Firstname")
txtLastName.Text = StudentDatabase.Fields("Lastname")
txtMajor.Text = StudentDatabase.Fields("Major")
txtGradDate.Text = StudentDatabase.Fields("GradDate")

We are retrieving the information through the recordset, not through the connection which we used to execute our queries. Suppose we want to move through the various records. This can be accomplished with the Visual Basic functions .MoveNext, .MovePrevious, .MoveFirst, and .MoveLast. To move to the next field we can set up a button that corresponds to a function that runs this code:

StudentDatabase.MoveNext

We should set up four buttons to accomplish these tasks, cmdNext, cmdPrevious, cmdFirst, and cmdLast.

Now that we can move through the fields we may wish to delete records within the database. The following function will delete an entire record (SSN, Firstname, Lastname, Major, and GradDate) based upon our primary key, or the Social Security Number. We need to set up a button, cmdDelete that will run this function.

Dim SQLStmt As String
SQLStmt = "DELETE FROM Students WHERE SSN = '" & txtSSN.Text & "' "
StudentDatabaseConnection.Execute SQLStmt

The last thing we will add to our program is the Exit command. When we exit the program we must end our connection to the recordset and database. This can easily be accomplished with these two lines of code:

StudentDatabase.Close
StudentDatabaseConnection.Close

We have now completed our rudimentary Visual Basic program to access a database through ODBC on a SQL Server. We can add, delete, and peruse records. Other function, such as update and creation of database tables can be added easily later.

One more acronym please!

Remember I said that there are three strengths to BASIC, and I only mentioned two. Well, here’s the last and a very important one. Within the last couple of years a language has beendeveloped known as Visual Basic Script, or VBScript that can run programs through web browsers such as Netscape, Opera, and Microsoft Internet Explorer. This makes it easy to transport some programs developed in Visual Basic to the web and vice-versa. Not all features are supported in VBScript. For instance the ability to read and write files through the web browser is extremely limited so that violations of security, viruses are more difficult to program and are not as frequent. Database programs, however, are easy to transport and benefit the most in conversions. This allows for data in a database is now accessible worldwide instead of companywide.

VBScript is readable by web browsers as ASP (Active Server Page) files on a Microsoft Internet Information Server (version 3.0 and later of course). Instead of index.html we now have index.asp. The languages that we must be familiar with also now change. We must still be familiar with Visual Basic (ok, actually VBScript) and SQL, however, we just added HTML (Hyper Text Markup Language). Note: HTML is a fairly simple language to learn. If you would like more information, there are many good primers on the web.

In Visual Basic we have forms, now our forms in a small sense become web pages written in HTML. The way it works is that the scripts are executed and it sends the information to the web browser in HTML form. None of the scripting language source is viewable when it is sent, only the HTML. For instance, here is an extremely short ASP program that will demonstrate the HTML output.

<%@ LANGUAGE="VBScript" %>
<% 

Option Explicit
Dim i
     For i= 1 to
5

%> <fontsize=<%=i%>>Hello</font>


<%
     Next
%>

Hello
Hello
Hello
Hello
Hello

If you choose to view the source the code looks like this:

<font size=1>Hello<br></font>
<font size=2>Hello<br></font>
<font size=3>Hello<br></font>
<font size=4>Hello<br></font>
<font size=5>Hello<br></font>

Bring on the Databases

<%@ LANGUAGE="VBScript" %>

<%
Option Explicit

Dim Connection
Dim RS
Dim SQLStmt
Dim SSN, Firstname, Lastname

Set Connection = Server.CreateObject("ADODB.Connection")

Connection.Open "DSN=Students; UID=sa"

	SQLStmt = "SELECT SSN, Firstname, Lastname "
	SQLStmt = SQLStmt & "FROM Students "
	SQLStmt = SQLStmt & "ORDER BY Lastname "

	Set RS = Connection.Execute(SQLStmt)<br>
%>

<html>
<title>Administration Student Update</title>
<BODY BGCOLOR="FFFFFF">

<hr>
<font size=2>
<h1>Student Information</h1>
<h2>Here are the students in the Database.
 Click a name to update the student information:</h2>

  <%Do While

Not RS.EOF%> Name:<ahref="details1.asp?SSN=<%= RS.Fields("SSN") %>">
<b><%= RS.Fields("Lastname")%>, <%= RS.Fields("Firstname") %></b></a>

<%   RS.MoveNext
     Loop
%>

<hr>
</body>
</html>

<%	Connection.Close %>

<%@ LANGUAGE="VBScript" %>

<%
Option Explicit
Dim UID, SQLStmt, Connection, FirstName, LastName
Dim RS, SSN, Major, GradDate

	SSN = Request.QueryString("SSN")
	Set Connection = Server.CreateObject("ADODB.Connection")

	Connection.Open "DSN=Students; UID=sa"

	SQLStmt = "SELECT * "
	SQLStmt = SQLStmt & "FROM Students "
	SQLStmt = SQLStmt & "WHERE SSN = '" & SSN & "' "
	Set RS = Connection.Execute(SQLStmt)

%>

<html>
<title>Administration Student Update</title>
<BODY BGCOLOR="FFFFFF">

<hr>
<font size=2>
<h1>Student Information</h1>

<%	Do While Not RS.EOF%>
<h2>Here is the Student Information for 
<%= RS.Fields("Firstname") %> <%= RS.Fields("Lastname") %>:</h2>

<b>First Name:</b> <%= RS.Fields("Firstname") %><p>
<b>Last Name:</b> <%= RS.Fields("Lastname") %><p>
<b>Social Security Number:</b> <%= RS.Fields("SSN") %><p>
<b>Major:</b> <%= RS.Fields("Major") %><p>
<b>Graduation Year:</b> <%= RS.Fields("GradDate") %><p>
<%	RS.MoveNext
	Loop
%>

<hr>
</body>
</html>
<%	Connection.Close %>

By using query strings information can continually be passed between web pages on a site so that information can be retained.

One of the many beauties of ASP is that the web pages will never increase in size as the database grows as they are dynamic. Data can change and the outputted web pages will change, but the original ASP files will stay the same. Another plus about ASP is that if the script is run on the server as all the scripts demonstrated do, the programs can be used on various platforms. As long as the browser can read the HTML, there is no limit on operating system or machine. Also, since all the ASP files are generally in one location all the files can be updated easily, so there are no patches or version updates that need to be released to customers. ASP is currently being used on E-Commerce sites and other database sites and it is quickly growing. One example is http://www.headhunter.net/.

Search the Web. Type it and go!