Working with Databases

 

No matter what programming language you use at some point and time you will need to access a database. While you don't have to be a database whiz you should at least, know the basics of working with databases.

The first thing you will need to understand is the basics of SQL (Structured Query Language ). SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data.

This tutorial is not a Database programming or Database Design tutorial. Its purpose is to give you a basic understanding of SQL so you can work with databases in your applications. Whether your language of choice is VB, C/C++ or Java. Or your database of choice is Access, Oracle, Microsoft SQL, MSSQL or MySQL. This tutorial should help you with accessing databases.

For the purpose of this tutorial I will be demostrating examples in VB and Java. These examples have be compiled using VB5/6 and Java 1.3.1 and Access 97.

SQL stands for "Structured Query Language". This language allows us to pose complex questions of a database. It also provides a means of creating databases. Many database products support SQL, this means that if learn how to use SQL you can apply this knowledge to countless other databases.

SQL works with relational databases. A relational database stores data in tables (relations). A database is a collection of tables. A table consists of a list of records - each record in a table has the same structure, each has a fixed number of "fields" of a given type.

SQL also has commands to create tables, to add records, to delete records and to change the value of fields of existing records; you can specify read and write permissions for other users; you can commit and roll back transactions; you can add and delete fields from existing tables, specify indexes and create views.

SQL is easy to learn and because of that, with the information I provide here you will be able to add, update, retrieve and delete records in any database. Element K has two SQL self-study courses as well as two instructor-led courses. If you have access to the self-study course I would recommend that you take a look. The SQL courses links below.

SQL Fundamentals of Querying
SQL Advanced Querying

 

Select

When querying a database you must tell the database what record(s) you want to view, add, edit or delete. You do this by using the Select statement. With "Select", you can tell the database what table you will be querying.

Say we have a database that has a table named Employees and has the following records.

FistName LastName Address Phone SSNumber

To retrieve the Employees table all we have to do is query the database with the following statement.

Select * From Employees;

The wild card character (*) allows you to retrieve all the records in a table with out listing them one by one. With out the wild card the statement would look like the statement below.

Select FirstName, LastName, Address, Phone, SSNumber From Employees;

Lets say we only want a list of employees first and last names, the statement would look like this:

Select FistName, LastName From Employees;

From

You can not have a SQL statement with out using "From", since the From clause tells the database what table you are querying. If you just typed, "Select FirstName, LastName" the database would not know where to find that information. So the "From" clause tells the database what table you are selecting your records from. So the statement below tells the database that you are selecting all the records in the Employees table.

Select * From Employees

 

Quick Test

Using the table below use the Select From statement to retrieve the employees first and last name and SS#

Employees
FirstName	LastName	Address	Phone	SSNumber
John	Doe	123 Elm St	408 225-0000	223-23-1631
Jane	Doe	5525 Oak LN	415 332-1250	332-22-6657
John	Smith	3325 LakeView Dr	880 557-5484	552-11-3321
Jane	Smith	882 S Main	303 377-5774	441-55-8141

 

Conditional Selection

There will be times when you want to filter your queries to the database, this can be done using "Where" and "Like" and by using the "And" and "Or " operators.

Where clause

The "Where" clause is used as a filter. So if you only want to retrieve records "Where " the last name is Smith then the Select statement would look like this.

Select * From Employees Where LastName = 'Smith';

As you can see we used the equal sign (= ) to tell the database the last name should equal Smith. Below is the list of Conditional selections. Also you will notice that I used single quotes around the name, This is required when querying a string.

=	Equal
<> or != (see manual)	Not Equal
<	Less Than
>	Greater Than
<=	Less Than or Equal To
>=	Greater Than or Equal To
Like	See Below

Lets say we want to retrieve a list of employees who's first name is John.

Select * From Employees Where FirstName = 'John';

Like operator

The Likeoperator like the Where clause filters your request to the database. Say you only want to view a list of employees who's last name start with a "M" or end with a "S", then you would use the Like command with your Select statement.

Select * From Employees Where LastName Like 'S%';

The above statement tells the database that you want a list of all employees who's last name starts with a "S". In SQL the percent(%) key is used as a wild card. So you will get a list of employees who's last name starts with a "S", no matter what precedes the "S" it could be numbers or letters. Once again it will depend on your database as to what wild card you use. If you are accessing an Access DB then the asterisk (*) would be used.

Select * From Employees Where FirstName Like '%W';

The above statement tells the database that you want a list of all employees who's first name ends with a "W". Notice that the percent(%) key is now in front of the letter we want to filter. If you want to find an employee who had a "D" in there last name you would use the following statement.

Select * From Employees Where LastName Like '%D%';

Other possibilities of using LIKE, or any of these discussed conditionals, are available, though it depends on what DBMS you are using; as usual, consult a manual for the available features on your system, or just to make sure that what you are trying to do is available and allowed.

 

And & Or

The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed (i.e. all conditions hold true). The OR operator joins two or more conditions, but returns a row if ANY of the conditions listed hold true.

To further discuss the SELECT statement, let's look at a new example table

EmployeesStats
EmployeeID	Salary	Benefits	EmpPosition
010	75000	15000	Manager
105	65000	15000	Manager
152	60000	15000	Manager
215	60000	12500	Manager
244	50000	12000	Staff
300	45000	10000	Staff
335	40000	10000	Staff
400	32000	7500	Entry-Level
441	28000	7500	Entry-Level

Lets say we want a list of employees who has a salary over 40k and is part of the staff, you would use the following select statement using the And operator.

SELECT * FROM EmployeesStats
WHERE Salary > 40000 AND EmpPosition = 'Staff';

Lets say we want a list of employees who make less then 40k or have benefits less then 10k. We would use the select statement using the Or operator.

SELECT * FROM EmployeesStats
WHERE Salary < 40000 OR Benefits < 10000;

You can also combine the And and Or operators to further filter your query. Lets say you want to a list of employees how are Managers and have a salary over 65k or have benefits over 12.5k. You would use the following select statement.

Select * From EmployeesStats
Where EmpPosition = 'Managers' And Salary > 65000 Or Benefits > 12500;

First, SQL finds the rows where the salary is greater than $65,000 and the position column is equal to Manager, then taking this new list of rows, SQL then sees if any of these rows satisfies the previous AND condition or the condition that the Benefits column is greater than $12,500. Subsequently, SQL only displays this second new list of rows, keeping in mind that anyone with Benefits over $12,500 will be included as the OR operator includes a row if either resulting condition is True. Also note that the AND operation is done first.

 

Quick Test

Using the above Employees table what would be the results of the three(3) select statements?

 

Final words on SQL

We will rap up this quick introduction to SQL by demonstration Adding, Editing and Deleting data in the database.

Adding Data

When working with a database you will need to add data at some point. To add data you will use the Insert Into statement. When inserting new records you will also need to use the Value command. Using our previous Employees table we will use the statement below to add and employee to the Employees table.

Insert Into EmployeesStats Values (500, 35000, 10000, 'Staff');

The above code will add EmployeeID with the number of 500, Salary of 35k , Benefits of 10k and EmpPosition of Staff. You may also notice that the order of insertions are according to the way the database is set up. Once again when strings are involved single quotes are used.

 

The Updated Database

EmployeesStats
EmployeeID	Salary	Benefits	EmpPosition
010	75000	15000	Manager
105	65000	15000	Manager
152	60000	15000	Manager
215	60000	12500	Manager
244	50000	12000	Staff
300	45000	10000	Staff
335	40000	10000	Staff
400	32000	7500	Entry-Level
441	28000	7500	Entry-Level
500	35000	10000	Staff

Depending on how the database was designed the record can either be placed at the end of the records, as it did here. Or it can be placed at the end of the "Staff" records.

The following Insert Into statement will also work.

Insert Into EmployeesStats (EmployeeID, Salary, Benefits)
Value (500, 35000, 10000);

What you will notice, we did not add the EmpPosition. This was done to show you can add only pacific records. But once again, it would also depend on how the database was design. It could be that the database was designed not to allow null records for the EmpPosition. Meaning you must enter the EmpPosition or the database will not let you add the record. In that case the Insert Into statement would look like this:

Insert Into EmployeesStats (EmployeeID, Salary, Benefits, 'EmpPosition')
Value (500, 35000, 10000, 'Staff');

Deleting Data

When deleting records do it with caution! Once you delete a record you can not go back and undelete it. This would be a good place to add code in your application to verify the deletion before the record is actually deleted.

To delete a specific record you would use the Delete From statement. To delete the record we just created, we would use the following Delete statement.

Delete From EmployeesStats
WHERE EmployeeID = 500;

Now assuming that the database was design so the EmployeeID would be unique, the above statement will delete Employee number 500. However you can never be too careful when deleting records, so you can be more specific.

Delete From EmployeesStats
Where EmployeeID = 500 And Salary = 35000 And Benefits = 10000 And EmpPosition ='Staff' ;

Now the record will not be deleted unless all the above statements are true. It requires more typing but remember if you delete a record by mistake there's not going back and recovering that record.

Lets say we decided to get rid of all managers and remove them from the database, then the following Delete Statement would work.

Delete From EmployeesStats
Where EmpPosition = 'Manager';

Note: Omitting the Where clause will cause all EmployeesStats records to be deleted so be careful!

Updating Data

There will be times when you need to update a records. An example of this is when an employee receives a raise. You don't what to delete their record and add a new one just to change their salary, that wouldn't be very smart. That is where the Updating clause comes in. To update the salary of the employee we just added we would do the following.

Update EmployeesStats Set Salary = 40000 Where EmployeeID = 500;

You will need conditionals in order to make sure you are increasing the correct employee salary. Here we used the Where clause. Additional records can be set by separating equal statements with commas.

Update EmployeesStats Set Salary = 40000, EmpPosition = 'Manager' Where EmployeeID = 500;

With the above statement you are changing the employees salary and position of employee number 500.

Quick Test

Using the EmployeesStats table above write statement that will add an employee with the following values

EmployeeID = 550
Salary = 27500
Benefits = 10000
Position = Staff

Using the EmployeesStats table write a statement that will delete employee number 010

Using the EmployeesStats table write a statement that will change employee 335 salary to 60000 and EmpPosition to Manager.

 

Final Notes

Learning basic SQL will allow you to work with just about any database as long as your database is ANSI (American National Standards Institute) standard. Here we only went over the very basic SQL commands, however with these commands you can add, update, delete and view any record in a database.

If you think you will be working with databases in the future I would suggest that you fully learn SQL. It only takes a few hours to learn, yes it is that easy. The trick may be incorporating it with your Database, but like I stated earlier as long as your database is SQL92 compliant you shouldn't have any major problems. The only problems that might arise are the characters used in some of the statements.

Example: The Like wild card search

SQL uses the percent(%)
Unix uses (*) or (?)

Some of the clauses and functions we did not go over

Distinct - Distinct is used to eliminate duplicate records.
Group By - The GROUP BY clause will gather all of the rows together that contain data in the specified column(s)
Order By - ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by.
Having - The HAVING clause allows you to specify conditions on the rows for each group
Join - Joins allow you to link data from two or more tables together into a single query result - from one single SELECT statement.


IN and BETWEEN Conditional Operators

In - The IN conditional operator is really a set membership test operator. That is, it is used to test whether or not a value (stated before the keyword IN) is "in" the list of values provided after the keyword IN.
Between - The BETWEEN conditional operator is used to test to see whether or not a value (stated before the keyword BETWEEN) is "between" the two values stated after the keyword BETWEEN

Aggregate functions

MIN	returns the smallest value in a given column
MAX	returns the largest value in a given column
SUM	returns the sum of the numeric values in a given column
AVG	returns the average value of a given column
COUNT	returns the total number of values in a given column
COUNT(*)	returns the number of rows in a table

Accessing a Database using VB

Microsoft has make it very easy to access databases using VB. At its basic level all you have to do is add the data control and point it to the database. We will look at several ways to access and manipulate a database using VB namely ADO, DAO and the Data Control.

The Microsoft Jet database engine that is supplied with VB allows you to access many types of databases -- MSAccess, dBase, FoxPro, Paradox and Btrieve. And any relational database that supports the open database connectivity (ODBC) standard.

My first venture in to accessing a database was using the Data Control, it was quick easy and painless. One of the first applications I developed was a Video cataloging program, with over 600 movie videos I need a program to put them in order and the ability not only print out a list, but be able to find a movie as quickly as possible.

 

Data Control

As stated earlier, the Data Control is the easiest way to access a Database. You add the Data Control to your project and link it to the database. This can be done by using the property window or through code. These demos will be using code. We will be using the database table below to query the database. The way this database is set up the CatNo is Primary Key and will not allow duplicate catalog numbers, The Title, Star, CoStar and Type are all set up as strings and the rDate is set up as a Date format.

Video.mdb

Movies
CatNo	Title	Star	CoStar	rDate	Type
S0001	The Net	Sandra Bullock	Jeremy Northam	1995	A
S0002	Independence Day	Will Smith	Bill Pullman	1996	S
S0003	Jerry Maguire	Tom Cruise	Cuba Gooding, Jr	1996	C
S0004	Miss Congeniality	Sandra Bullock	Michael Caine	2000	C

The quickest way to display records in a database is to use the DBGrid in conjunction with the Data Control. So add the DBGrid to your your application along with the Data Control and a Command button. Set the DataSource of the DBGrid to Data1(This will link the DBGrid to the database through the Data Control). Now using code we will have all the records in the database displayed in the DBGrid.

Private Sub Command1_Click()

Data1.DatabaseName = App.Path & "\video.mdb" 'This tells the Data control where to find the database.
Data1.Connect = ";pwd=password" 'This tells the Data1 control its connecting to password protected Access Database.
Data1.RecordSource = "Select * From Movie" 'This tells the Data control what table to pull the records from.
Data1.Refresh 'When ever you use the data control you must use the Refresh method.
End Sub

What the above code will do is display all the records in the Move table in the DBGrid when you click on the command button.

Lets say we only what to view the list of movie that have Sandra Bullock in them. All we have to do is use the Where clause. Add a text box to your application, this will be use to hold your query. Now use the following code.

Private Sub Command1_Click()

Data1.DatabaseName = App.Path & "\video.mdb"
Data1.Connect = ";pwd=password"
Data1.RecordSource= "Select * From Movie Where Star ='" & Text1 & "'"
Data1.Refresh
End Sub

Now to retrieve all the movies that star Sandra Bullock just type her First and Last name in the text box. If you want a list of movies that have stars with Tom in there name, then we would use the Where and Like clause.

Private Sub Command1_Click()

Data1.DatabaseName = App.Path & "\video.mdb"
Data1.Connect = ";pwd=password"
Data1.RecordSource= "Select * From Movie Where Star Like'*" & Text1 & "*'"
Data1.Refresh
End Sub

Now if you type Tom in the text box all movies that have Tom as the star will be displayed.

You may have noticed that I use the Connect property. I did this to show you it is possible to access a password protected database. If the database is not password protected then you would not include the Data1.Connect statement.

Move methods

Since not all your records will be displayed in the DBGrid, you will need to use the MoveFirst, MoveLast, MoveNext and MovePrevious methods. If you use text boxes to display records you will need this methods in order to move through the records in the database.

Our database has six fields, CatNo, Title, Star, CoStar, rDate and Type so you will need six text boxes to hold the data in the database. You then must link the text boxes to the database via the Data Control, you do this by adding the Data Control to your project then select all the text boxes and in the property window select "DataSource" and select Data1 (make sure you set the visual property of the Data control to false). Next you must bind the text boxes with the each of the six database fields, this is done by selecting the first text box and typing CatNo in the "DataField" property of the text box. Do this for each of the text boxes (i.e. the second text box "Title" should be typed in the DataField property). Now put 4 command buttons and label them First, Previous, Next and Last. Your form should look like the figure below.

 

Private Sub Command1_Click()
Data1.Recordset.MoveFirst
End Sub

Private Sub Command2_Click()
If Not Data1.Recordset.BOF Then Data1.Recordset.MovePrevious
End Sub

Private Sub Command3_Click()
If Not Data1.Recordset.EOF Then Data1.Recordset.MoveNext
End Sub

Private Sub Command4_Click()
Data1.Recordset.MoveLast
End Sub

Private Sub Form_Load()
Data1.DatabaseName = App.Path & "\video.mdb"
Data1.RecordSource = "Select * From Movies"
Data1.Refresh
End Sub

 

Data Access Object (DAO)

DAO is a high-level object interface that was originally designed for use with Microsoft Access databases. It uses the Microsoft Jet engine to access data. Developers have used DAO with non-Microsoft databases; however, it has not been as efficient as some of the other available access methods. Recent improvements to Jet have made its performance significantly better. For small applications, DAO works quite nicely.

While you don't need to add a control to use DAO you do have to make a reference to it in your project. This is done by selecting Projects | References then select "Microsoft DAO 3.51 Object Library".

Using the same database as the previous examples to load all the records in the database using DAO do the following.

Private Sub Command1_Click()
Dim db As Database
Dim rs As RecordSet
Dim sql As String

sql = "Select * From Movies"
Set db = OpenDatabase(App.Path & "\video.mdb", False, False, ";pwd=password")
Set rs = db.OpenRecordSet(sql, dbOpenDynaset)

Do While Not rs.EOF

List1.AddItem rs("CatNo") & vbTab & rs("Title") & vbTab & rs("Star") & vbTab & rs("CoStar") & vbTab _ & rs("rDate") & vbTab & rs("Type")
rs.MoveNext
Loop

rs.Close
db.Close
End Sub

First we create a database and recordset object

Then we declare a string to hold the SQL statement

We create the SQL Statement

We then assign object references to variables in this case db to the Database Object and rs to the RecordSet object.

We then move through each of the records until it reaches the end of the file or last record (rs holds all the records)

We then place each of the records in the appropriate position using the List box

The records will be placed one row at a time, this will be done until all records have been added to the list box.

We then close the RecordSet and Database.

That's it, the above code will display all the records in the Movies table to the List box

 

Open Statements

OpenDatabase

The OpenDatabase method syntax has these parts
The Database Object - In this case db (remember we set the Database object to db).
WorkSpace - This is optional if you did not include a workspace OpenDatabase use the default workspace.
dbName - This is the name and location of the Database or the the data source name (DSN).
Options - Optional. A Variant that sets various options for the database, as specified in Settings.
Read-Only - Optional. Set True if you want to open the Database as read only False if you want read/write access.
Connect - Optional. Specifies various connection information, including passwords. This can be use to connect to any ODBC compliant database.

OpenRecordSet

The OpenRecordset method syntax has these parts
RecordSet - An object variable that represents the Recordset object you want to open
Object - An object variable that represents an existing object from which you want to create the new Recordset
Source - A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-type Recordset objects in Microsoft Jet databases, the source can only be a table name.
Type * - Optional. A constant that indicates the type of Recordset to open. You can use one of the following constants.

dbOpenTable - Opens a table-type Recordset object (Microsoft Jet workspaces only).
dbOpenDynamic - Opens a dynamic-type Recordset object, which is similar to an ODBC dynamic cursor.(ODBCDirect workspaces only)
dbOpenDynaset - Opens a dynaset-type Recordset object, which is similar to an ODBC keyset cursor.
dbOpenSnapshot - Opens a snapshot-type Recordset object, which is similar to an ODBC static cursor.
dbOpenForwardOnly - Opens a forward-only-type Recordset object

*Note If you open a Recordset and you don't specify a type, OpenRecordset creates a table- type Recordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset- type RecordSet. Since I used a query in my statement I could have omitted the "dbOpenDynaset" and OpenRecordset would have used it by default.

**Options - Optional. A combination of constants that specify characteristics of the new Recordset. You can use a combination of the following constants.

dbAppendOnly - Allows users to append new records to the Recordset, but prevents them from editing or deleting existing records (Microsoft Jet dynaset-type Recordset only).
dbSQLPassThrough - Passes an SQL statement to a Microsoft Jet-connected ODBC data source for processing (Microsoft Jet snapshot-type Recordset only).
dbSeeChanges - Generates a run-time error if one user is changing data that another user is editing (Microsoft Jet dynaset-type Recordset only). This is useful in applications where multiple users have simultaneous read/write access to the same data.
dbDenyWrite - Prevents other users from modifying or adding records (Microsoft Jet Recordset objects only).
dbDenyRead - Prevents other users from reading data in a table (Microsoft Jet table-type Recordset only).
dbRunAsync - Runs an asynchronous query (ODBCDirect workspaces only).
dbExecDirect - Runs a query by skipping SQLPrepare and directly calling SQLExecDirect (ODBCDirect workspaces only). Use this option only when you're not opening a Recordset based on a parameter query. For more information, see the "Microsoft ODBC 3.0 Programmer's Reference."
dbInconsistent - Allows inconsistent updates (Microsoft Jet dynaset-type and snapshot-type Recordset objects only).
dbConsistent - Allows only consistent updates (Microsoft Jet dynaset-type and snapshot-type Recordset objects only).

**Note The constants dbConsistent and dbInconsistent are mutually exclusive, and using both causes an error. Supplying a lockedits argument when options uses the dbReadOnly constant also causes an error.

Lockedits - Optional. A constant that determines the locking for the Recordset. You can use the following constants.

dbReadOnly - Prevents users from making changes to the Recordset (default for ODBCDirect workspaces). You can use dbReadOnly in either the options argument or the lockedits argument, but not both. If you use it for both arguments, a run-time error occurs.
dbPessimistic - Uses pessimistic locking to determine how changes are made to the Recordset in a multi-user environment. The page containing the record you're editing is locked as soon as you use the Edit method (default for Microsoft Jet workspaces).
dbOptimistic - Uses optimistic locking to determine how changes are made to the Recordset in a multi-user environment. The page containing the record is not locked until the Update method is executed.
dbOptimisticValue - Uses optimistic concurrency based on row values (ODBCDirect workspaces only).
dbOptimisticBatch - Enables batch optimistic updating (ODBCDirect workspaces only).

I know this may look a little daunting but its not. Actually you'll find that most of your statements will use the default values. I would say that 98 % of my access statements look like this:

Private Sub Command1_Click()
Dim db As Database
Dim rs As RecordSet
Dim sql As String

sql = "Select * Statement"
Set db = OpenDatabase(App.Path & "\dbname.mdb", False, False, ";pwd=password")
Set rs = db.OpenRecordSet(sql)

 

More DAO

The reset of our examples using DAO will be adding, deleting and editing records. We will add 7 Text boxes, 1 List box and two Command buttons. We will use these to add records and search for records in the database. The search box will look up the star of the movie. The form should look like this.

Code to Add records
Private Sub Command1_Click()
Dim db As Database
Dim rs As RecordSet

Set db = OpenDatabase(App.Path & "\video.mdb", False, False, ";pwd=password")
Set rs = db.OpenRecordSet("Movies")

rs.AddNew
rs("CatNo") = txtCatNo
rs("Title") = txtTitle
rs("Star") = txtStar
rs("CoStar") = txtCoStar
rs("rDate") = txtDate
rs("Type") = txtType
rs.Update

rs.Close
db.Close
End Sub

Code to Search records
Private Sub Command2_Click()
Dim db As Database
Dim rs As RecordSet
Dim sql As String

List1.clear

sql = "Select * From Movies Where Star =' " & txtSchStar & " ' "
Set db = OpenDatabase(App.Path & "\video.mdb", False, False, ";pwd=password")
Set rs = db.OpenRecordSet(sql)

Do While Not rs.EOF

List1.AddItem
rs("CatNo") & vbTab & rs("Title") & vbTab & rs("Star") & vbTab & rs("CoStar") & vbTab _
& rs("rDate") & vbTab & rs("Type")
rs.MoveNext
Loop

rs.Close
db.Close
End Sub

Once again we create a Database and Recordset object and since we will be querying the database we declare a string to hold the query (sql). Since you can search more then once you want to clear the List box for each search so I called the (List1.clear). When the command button is clicked the database will look for the Star that is typed in the txtSchStar text box. It will then display the results in the list box. The vbTab will put a tab between each record. By using the vbTab I can line up each of the columns using the TabStops Function below.

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Public Const LB_SETTABSTOPS = &H192

Private Function TabStops()
'Set up tab stops in the list box
ReDim TabStop(1 To 5) As Long

'Set the tab stop points.
TabStop(1) = 40
TabStop(2) = 140
TabStop(3) = 210
TabStop(4) = 280
TabStop(5) = 310

'Send LB_SETTABSTOPS message to ListBox.
Call SendMessage(List1.hwnd, LB_SETTABSTOPS,5, TabStop(1))

'Refresh the ListBox control.
List1.Refresh
End Sub

Now type TabStops under Form_Load, when you do a search your List box display will go from this to this.

Code to Delete records
Private Sub mnuDelete_Click()
Dim db As Database
Dim sql, dCatno As String
Dim noAns As Integer

dCatno = InputBox(Please enter the the Catalog number you wish to delete")
noAns = MsgBox("Are you sure you want to delete this record?, vbYesNo)
If noAns = 7 Then MsgBox "Delete has been cancelled":Exit Sub
sql = "Delete * From Movies Where CatNo='" &dCatno &"'"
Set db = OpenDatabase(App.Path & "\video.mdb", False, False, ";pwd=password")
db.Execute sql
MsgBox "Record has been deleted"
db.Close
End Sub

Here we used the inputbox to get the catalog number that you want to delete. Since you cant undo a delete we add code to make sure you want that record deleted. If the user changes there mind clicking the "No" button will cancel the delete by exiting the sub procedure. The vbYesNo button can produce 1 of 2 integers 6 = Yes and 7 = No, if the user confirms the deletion they will press the "Yes" button which will produce a 6 and the record will be deleted. We then create the SQL statement to delete the record, we delete the record that is input in the InputBox. Then if "Yes" is clicked we Execute the sql statement to delete the record, then we confirm the deletion.

Code to Edit records
Command3_Click()
Dim db As Database
Dim rs As RecordSet

Set db = OpenDatabase(App.Path & "\video.mdb", False, False, ";pwd=password")
Set rs = db.OpenRecordSet("Movies")

rs.Edit
rs("CatNo") = txtCatNo
rs("Title") = txtTitle
rs("Star") = txtStar
rs("CoStar") = txtCoStar
rs("rDate") = txtDate
rs("Type") = txtType
rs.Update

rs.Close
db.Close
End Sub

This code assumes that you have already loaded the record you wish to change in the textbox. This code looks just like the code you use to add to the database but, "Edit" is used instead of AddNew.

 

Final Thoughts

DAO is very easy to use and is great for working with Access and other ISAM(Indexed Sequential Access Method) file types. ISAM is a file management system developed at IBM that allows records to be accessed either sequentially (in the order they were entered) or randomly (with an index).

 

What is ADO

ADO which stands for ActiveX Data Objects is a Microsoft technology that is a programming interface to access databases. ADO was designed to be a high-level interface to provide ease of access to data stored in a wide variety of database sources. The earlier versions of ADO could only access relational databases with fixed columns and data types. However, since the release of version 2.5, ADO is now able to interface not only with relational databases, but also with non-relational databases, folders, data files, and even e-mail messages. For example, ADO can now handle data that are contained in a tree-like structure with a root, nodes, and leaves. It can also handle data where the number of columns and the data type vary from row to row. This represents a significant improvement in the usefulness and value of ADO.

ADO is part of Microsoft Data Access Components (MDAC) and can be downloaded here for free. The latest version of MDAC is 2.6.

ADO can be used with a variety of programming languages, including Visual Basic, VBScript, JScript, Visual C++, and Visual J++. Further, by using ASP, you can reference ADO components on HTML-generated Web pages.

 

Accessing a Database using ADO

Like DAO you must add a reference to ADO in your project, this is done my selecting Project | References | Microsoft ActiveX Data Objects 2.6 Library. We can now start using ADO in our program.

ConnectingString

When we used DAO we used the OpenDatabase method to connect to the database. However ADO does not have a OpenDatabase method, ADO uses a Connection String to connect to the database. The ConnectionString property can be used to set or return a string that contains the information needed to establish a connection to a data source.

ADO supports five arguments for the connection string:

File Name= is the name of a file that contains the connection information. If you use this parameter, you cannot use the Provider= parameter.

Provider= is the name of the provider. If you use this parameter, you cannot use the File Name = parameter.

Remote Provider= is the name of the provider that you use when you open a client-side connection. (for Remote Data Service)

Remote Server= is the path name to the server that you use when you open a client-side connection. (for Remote Data Service)

URL= is the absolute URL address to use for the connection.

Here are some ConnectionString examples:

Provider for Active Directory Service:
"Provider=ADSDSOObject;"

OLE DB Provider for Index Server:
"Provider=MSIDXS;"

OLE DB Provider for Internet Publishing:
"Provider=MSDAIPP.DSO; Data Source=http://www.websitename.com/"

OLE DB Provider for Microsoft Jet:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbName.mdb;"

OLE DB Provider for ODBC Databases:
"Provider=MSDASQL; Driver={SQL Server}; Server=DBServer;
DataBase=GuruDatabase;"

OLE DB Provider for Oracle:
"Provider=MSDAORA; Data Source=SourceTable;"

OLE DB Provider for SQL Server:
"Provider=SQLOLEDB; Data Source=SourceName; Network Library=DBMSSOCN;
Initial Catalog=GuruPubs;"

The Provider property is used to set or return the name of the provider for a specific Connection object. The default is MSDASQL (Microsoft OLE DB provider for ODBC).

Provider Code	Provider
ADSDSOObject	Active Directory Services
Microsoft.Jet.OLEDB.4.0	Microsoft Jet databases
MSDAIPP.DSO.1	Microsoft Internet Publishing
MSDAORA	Oracle databases
MSDAOSP	Simple text files
MSDASQL	Microsoft OLE DB provider for ODBC
MSDataShape	Microsoft Data Shape
MSPersist	Locally saved files
SQLOLEDB	Microsoft SQL Server

Dim dbConnect
Set dbConnect = Server.CreateObject("ADODB.Connection")
dbConnect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbName.mdb;"
dbConnect.ConnectionTimeout = 0
dbConnect.Open

You can also use Driver in place of Provider

Dim Conn1 As New ADODB.Connection
Dim dbConn As String

dbConn = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=nwind.mdb;" & _
"DefaultDir=C:\vb;" & _
"Uid=Admin;Pwd=;"

dbConn = "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"

dbConn = "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"

dbConn = "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"

dbConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\somepath\;" & _
"Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"

Open Method

The Open method is called on a Recordset object to open a cursor which gives you access to the records contained in the base table, the results from a query, or a previously saved Recordset.

There are five optional parameters:
Source - Optional can be one of the following,Command object, SQL query string, table name, stored procedure call, URL, full or relative path/file name,Stream object containing a Recordset
ActiveConnection - Optional parameter is either a connection string that defines the connection, or it is a variant that contains the valid Connection object associated with the Recordset
CursorType - Optional is one of the CursorTypeEnum constants that specifies the type of cursor to use when you open a Recordset object.

Constant	Value	Description
adOpenDynamic	2	A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
adOpenForwardOnly	0	Default, a forward scrolling only, static cursor where changes made by other users are not visible
adOpenKeyset	1	A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
adOpenStatic	3	A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
adOpenUnspecified	-1	Cursor type not specified

LockType - Optional is one of the LockTypeEnum constants that indicates the type of lock in effect on a Recordset. The default is adLockReadOnly.

LockTypeEnum Constants

Constant	Value	Description
adLockBatchOptimistic	4	Multiple users can modify the data and the changes are cached until BatchUpdate is called
adLockOptimistic	3	Multiple users can modify the data which is not locked until Update is called
adLockPessimistic	2	The provider locks each record before and after you edit, and prevents other users from modifying the data
adLockReadOnly	1	Read-only data
adLockUnspecified	-1	Lock type unknown

Options - Optional tells the provider how to evaluate the Source parameter when it contains something other than a Command object. The appropriate use of this option can speed up performance since ADO will not have to determine the type of the data source. It can be one or more of the following CommandTypeEnum or ExecuteOptionEnum constants.

CommandTypeEnum Constants

Constant	Value	Description
adCmdFile	256	Evaluate as a previously persisted file
adCmdStoredProc	4	Evaluate as a stored procedure
adCmdTable	2	Have the provider generate an SQL query and return all rows from the specified table
adCmdTableDirect	512	Return all rows from the specified table
adCmdText	1	Evaluate as a textual definition
adCmdUnknown	8	The type of the CommandText parameter is unknown
adCmdUnspecified	-1	Default, does not specify how to evaluate

ExecuteOptionEnum Constants

Constant	Value	Description
adAsyncFetch	32	Records are fetched asynchronously
adAsyncFetchNonBlocking	64	Records are fetched asynchronously without blocking the return

Once again this may be a little daunting but remember most of the time you will use the default parameters. Now to open any of the above databases you would use the Open method.

conn1.Open dbConn

 

Viewing Records

We will be going over the code that will allow us to view, add, edit and delete records using the RecordSet.

RecordSet Properties

The following is a list of the properties available for a RecordSet, with a brief description of each:

AbsolutePage	Page of current position
AbsolutePosition	Current position
ActiveConnection	Active Connection Object
BOF	Beginning of File
Bookmark	Bookmark of current position
CacheSize	Number of records cached
CursorLocation	Server or Client
CursorType	Forward, static, dynamic, keyset
EOF	End of File
EditMode	Whether or not an edit is in progress
Filter	What kind of records to hide
LockType	Record lock type for edits or updates
MaxRecords	Maximum number of records to retrieve
PageCount	Total number of pages
PageSize	Number of records per page
RecordCount	Total number of records
Source	Source Command Object
Status	Status of last action

RecordSet Methods

AddNew	Add a new record to the RecordSet
CancelBatch	Cancel pending batch updates
CancelUpdate	Cancel pending update
Clone	Copy the RecordSet
Close	Close the RecordSet
Delete	Delete current record
GetRows	Retrieve multiple records
Move	Move the record pointer to a specific record
MoveNext	Move the record pointer to the next record
MovePrevious	Move the record pointer to the previous record
MoveFirst	Move the record pointer to the first record
MoveLast	move the record pointer to the last record
NextRecordSet	Load the next RecordSet in a multi-set query
Open	Open the RecordSet (execute the query)
Requery	Re-execute the last query executed
Resync	Synchronize the data with the server
Supports	Determine if a feature is supported by provider
Update	Update the current record
UpdateBatch	Update pending batched record updates

Once we connect to the Database we can now make queries. Using the video.mdb database we can get a list of all the records in the Movies table using the following code. Since I'm using a Access database we will use the Access Driver.

Dim Conn1 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim AccConn As String

AccConn = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=video.mdb;" & _
"DefaultDir=" & App.Path& ";" & _
"Uid=;Pwd=;"

Conn1.Open AccessConnect
Set rs = Conn1.Execute("SELECT * FROM Movies")
Do While Not rs.EOF
List1.AddItem rs(0) & vbTab & rs(1) & vbTab & rs(2) & vbTab & rs(3) & vbTab & rs(4)
rs.MoveNext
Loop
rs.Close
Conn1.Close

Noticed that I didn't add the record name in the recordset but the number. This can come in handy when you have an array of text boxes you want to load the records in. Like an array the database columns start at zero(0). So rs(0) = rs("CatNo").

 

Adding Records

This code will allow you to add records to the video.mdb database using AddNew.

Private Sub cmdAdd_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Con, sql As String
Dim i As Integer

Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=video.mdb;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=;Pwd=;"

sql = "Select * From Movies"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open Con
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
rs.AddNew
For i = 0 To 5
rs(i) = Text1(i)
Next i
rs.Update
rs.Close
cn.Close
'Clear text boxes
For i = 0 To 5
Text(i) = ""
Next i

End Sub

Version 2 for adding records to the database using SQL's Insert Into.

Private Sub cmdAdd2_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Con, sql As String
Dim i As Integer

Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=video.mdb;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=;Pwd=;"

sql = "INSERT INTO Movies Values" _
& "('" & Text1(0) & "','" & Text1(1) & "','" & Text1(2) & "','" & Text1(3) & "','" & Text1(4) & "','" & Text1(5) & "')"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open Con
rs.Open sql, cn, adOpenDynamic, adLockOptimistic

Set rs = Nothing
cn.Close
'clear text boxes
For i = 0 To 5
Text1(i) = ""
Next i
End Sub

Lets go over the above code, first we create the Connection and Recordset object. We then declare 2 strings, one to hold the database connection and the other to hold the recordset string. We also declared an integer, this will be used to clear the text boxes once the records are added to the database. (Here is where that textbox array comes in.)

We then create the Connection string, this string will tell what driver we will be using to connect, the name of the database, the user name and password if any. Since we did not have a user name and password attached to our database this parameters are left blank.

We then create the SQL statement that will add the records to the database. This is done by using the Insert Into SQL statement.

Now that we have everything declared and set, we then open the database and recordset connection, then we call the SQL Insert Into statement that will take the contents of the text boxes and add them to the database. We then clear the recordset and close the database and clear all the text boxes.

As you can see both methods will add the records to the database, the difference, is the second one can be ported to other databases besides Access. All we have to do is change the Driver or Provider and we can access a Oracle, MS SQL, MySql or any other ODBC compliant database.

 

Deleting Records

At some point and time your database may become filled with out dated records, who's only purpose is to take up space. When this happens you will need to delete the old records to make space for new ones. This is done by using the Delete statement. Don't forget that when you use the Delete statement that you need a Where statement (remember you don't want to delete all your records by mistake!).

Private Sub cmdDelete_Click()
' Delete a record and clear the textboxes
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Con, sql As String

Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=video.mdb;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=;Pwd=;"
sql = "Select * FROM Movies WHERE CatNo ='" & txtCatNo & "'"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open Con
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
rs.Delete
rs.Close
cn.Close
'Clear text box
txtCatNo = ""

The SQL version of the Delete statement

Private Sub cmdDelete_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Con, sql As String

Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=video.mdb;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=;Pwd=;"
sql = "Delete FROM Movies WHERE CatNo ='" & txtCatNo & "'"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open Con
rs.Open sql, cn, adOpenDynamic, adLockOptimistic

Set rs = Nothing
cn.Close
'Clear text box
txtCatNo = ""
End Sub

Updating Records

This code will allow you to update an existing record.

Private Sub cmdEdit_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Con, sql As String
Dim i As Integer

Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=video.mdb;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=;Pwd=;"
sql = "Select * From Movies Where CatNo='" & txtCatNo & "'"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open Con
rs.Open sql, cn, adOpenDynamic, adLockOptimistic

For i = 0 To 5
rs(i).Value = Text1(i)
Next i
rs.Update

'Clear text box
For i = 0 To 5
Text1(i) = ""
Next i
txtEdit = ""

Set rs = Nothing
cn.Close
End Sub

Here is the SQL version of the Update method

Private Sub cmdEdit_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Con, sql As String
Dim i As Integer

Con = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=video.mdb;" & _
"DefaultDir=" & App.Path & ";" & _
"Uid=;Pwd=;"

sql = "Update Movies Set CatNo='" & Text1(0) & "', Title='" _
& Text1(1) & "', Star='" & Text1(2) & "', CoStar='" & Text1(3) & _
"', rDate='" & Text1(4) & "', Type='" & Text1(5) & "' Where Title='" & txtTitle & "'"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open Con
rs.Open sql, cn, adOpenDynamic, adLockOptimistic

'Now clear the text boxes For i = 0 To 5
Text1(i) = ""
Next i

Set rs = Nothing
cn.Close
End Sub

Now lets go over the code to update the database. As always we create and set the database and recordset objects. We then declare the string variables to hold the connection and recordset strings, and the integer to clear the text boxes.

We then call the sql statement, in the first example it will hold all the records of the specified CatNo and then update them using the Value property and the Update method. In the second example the Update Set statement will update the records that are in the text boxes.

We then clear all the text boxes and close the database.

 

Summery

As you can see Microsoft has made it easy to work with databases using VB. At is basic level we can use the Data Control to view and manipulate records in the database. As the complexity of your application grows so can your tools to work with databases.

DAO is good for small application, like the video catalog program, it is quick and efficient. But the more complex your applications, such as Client/Server or Web development then ADO comes in to play.

Weather you are just starting out or a seasoned VB programmer, there are tools available to you to access and manipulate databases.

 

Accessing Databases with Java

If you are a Java programmer then the JDBC driver is your ticket to accessing and manipulating SQL databases. If you have Java 2 SDK on your computer then you already have the JDBC driver.

What is JDBC

JDBC (Java Database Connectivity) is designed to allow you to use SQL to query a wide variety of different database types. JDBC is supposed to free you from low-level concerns about particular database types. It should permit you, for example, to query an Access database with exactly the same source code as with an Oracle database.

JDBC is similar to Microsoft's ODBC (Open Database Connectivity), except that it is supposed to bring the added advantage of being platform independent(because it is written in Java).

Connecting

With Java there are two steps to connecting to a database. First you load the driver that will access your particular database then you establish the connection.

Class.forName - The name of the JDBC driver will be supplied to you by your database vendor
Connection - DriverManager.getConnection(String url, String username, String password);
The URL is the name of the database driver, if you are using the JDBC-ODBC Bridge driver, the JDBC URL will start with jdbc:odbc: . The rest of the URL is generally your data source name or database system

In keeping with the video database example, the code below connects to video.mdb database. I have set up a DSN(Data Source Name) and connected that to the path to the video database. If you don't want to set up a DSN you can edit the url String to the code below, this is called a DSN-less connection.

String url = jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=F:/VBdb/video.MDB";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:video";
Connection conn = DriverManager.getConnection(url,"","");

 

Retrieving Records

To retrieve records using Java you must create a ResultSet and Statement object . The ResultSet object will hold the Selection string and the Statement will then execute the the ResultSet.

//Select Query.java: Query an MS Access database using JDBC.

Retrieve all records

import java.sql.*;

class SelectQuery {

public static void main (String[] args) {

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:video";
Connection conn = DriverManager.getConnection(url,"","");
Statement stmt = conn.createStatement();
ResultSet rs;

rs = stmt.executeQuery("SELECT * FROM Movies");
while ( rs.next() ) {

String CatNo = rs.getString("CatNo");
String Title = rs.getString("Title");
String Star = rs.getString("Star");
String CoStar = rs.getString("CoStar");
String rDate = rs.getString("rDate");
String Type = rs.getString("Type");

System.out.println(CatNo + " " + Title + " " + Star + " " + CoStar + " " + rDate + " " + Type);

}
rs.close();
conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}

Retrieve a single record

import java.sql.*;

class SelectOneQuery {

public static void main (String[] args) {

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:video";
Connection conn = DriverManager.getConnection(url,"","");
Statement stmt = conn.createStatement();
ResultSet rs;

rs = stmt.executeQuery("SELECT * FROM Movies Where CatNo='S0010'");
while ( rs.next() ) {

String CatNo = rs.getString("CatNo");
String Title = rs.getString("Title");
String Star = rs.getString("Star");
String CoStar = rs.getString("CoStar");
String rDate = rs.getString("rDate");
String Type = rs.getString("Type");

System.out.println(CatNo + " " + Title + " " + Star + " " + CoStar + " " + rDate + " " + Type);

}
rs.close();
conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}

Depending on which Select Statement you use you can retrieve one or all the records in the database. As you can see the select statement is the same as DAO and ADO.

You can use the same code to open a Oracle database base or any database by just changing the JDBC driver. The code below will open a Oracle database.

class SelectQuery {

public static void main (String[] args) {

try {
Class.forName(oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:video";
Connection conn = DriverManager.getConnection(url,"","");
Statement stmt = conn.createStatement();
ResultSet rs;

As you can see this make porting your application to other database sources very easy.

 

Adding, Updating and Deleting Records

This section will cover adding, updating and deleting .

Adding a record

import java.sql.*;

class AddQuery {

public static void main (String[] args) {

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:video";
Connection conn = DriverManager.getConnection(url,"","");

String sql = "Insert Into Movies(CatNo, Title, Star, CoStar, rDate, Type) " +
"Values ('S0099', 'Chill Factor', 'Cuba Gooding Jr.', 'Skeet Ulrich', '1999', 'A')";

Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(sql);
conn.commit();
stmt.close();
conn.close();
System.out.println("Item had been Added");
}
catch (Exception e){
e.printStackTrace();
}
}
}

Updating records

import java.sql.*;

class UpdateQuery {

public static void main (String[] args) {

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:video";
Connection conn = DriverManager.getConnection(url,"","");

String sql = "Update Movies Set CatNo ='S0098' Where CatNo='S0099'";
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(sql);
conn.commit();
stmt.close();
conn.close();
System.out.println("Item had been updated");
}
catch (Exception e){
e.printStackTrace();
}
}
}

Deleting records

import java.sql.*;

class DeleteQuery {

public static void main (String[] args) {

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:video";
Connection conn = DriverManager.getConnection(url,"","");

String sql = "Delete From Movies Where CatNo='S0098'";
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(sql);
conn.commit();
stmt.close();
conn.close();
System.out.println("Item had been deleted");
}
catch (Exception e){
e.printStackTrace();
}
}
}

Have you noticed the only things I had to change in each of the source code? The SQL statement (String sql ), and the println (Print Line) string.

 

Summary

As you can see it's easy to work with databases using Java. If you have not taken the time to check out, "The Java Tutorial". Now is the time to do so, Sun has made this tutorial available for free, you can view it on line or download the full tutorial. They do have a section on JDBC Database Access.

I have only touched on the very basics of accessing and manipulating databases using Java. Take a look at the links below to further delve in to accessing databases using Java.

Sun's Java Tutorial
Getting Started with the JDBC API

 

Resources

Microsoft ADO
Microsoft ADO FAQ ADO FAQ
ADO and DAO examples
VB Database tips
Java JDBC Tips

Books
Visual Basic

Serious ADO: Universal Data Access with Visual Basic
by Robert MacDonald

ADO Examples and Best Practices
by William R. Vaughn

Visual Basic Developer's Guide to ADO
by Mike Gunderloy

DAO Object Model: The Definitive Guide
by Helen Feddema

Java

Database Programming with JDBC and Java, 2nd Edition
by George Reese

JDBC(TM) API Tutorial and Reference: Universal Data Access for the Java 2(TM) Platform (The Java(TM) Series)
by Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton, Mark Hapner

Understanding SQL and Java Together : A Guide to SQLJ, JDBC, and Related Technologies
by Jim Melton

 

In Conclusion

It doesn't matter if your language of choice is VB or Java, Sun and Microsoft has made it easier then ever to access and manipulate databases. And with scores of resources on the net, you are only a click away from getting information, tips and help on working with databases.

Good luck and have fun!

© 2001 Marietta Crockett
Disclaimer