 |
|
 |
|
|
|
|
|
|
 |
|
 |
| |||||||||||||||||||||
|
|
|
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". The first piece of code that is important to database connectivity through Visual Basic is the code that sets up the workspace. The workspace defines how your program will interact with the data from the database. It also sets up whether the connection type will be Microsoft Jet or ODBC. First we set up the variable and define it as a workspace.
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.
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.
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.
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.
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.
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.
Once the statment has been formed we must run the query against the
table. We accomplish this with the command:
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.
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:
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.
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:
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.
|
Section 2: SQL and Stuff Section 3: VBPower! Section 4: ASP Results Printable Version  
| ||||||||||||||||||||||
