tutorial application

Database Access with the Data Control

2011-12-23T20:33:00.000-08:00

STEPS:

1. Open a new Visual Basic project.

2. Put a data control (an intrinsic control, located in the VB toolbox) on the form and set the properties as follows:

Property	Value
(Name)	datAuthors
Caption	Use the arrows to view the data
Connect	Access (default)
DatabaseName	..\biblio.mdb
DefaultType	UseJet (default)
RecordSource	Authors (choose from list)

Notes: When you use the Data Control in a project, the properties that must be set are DatabaseName and RecordSource, in that order. DatabaseName is the name of the database you want to use, and the RecordSource is the name of the table in that database that you want to use.

Visual Basic & ADO Tutorial

2011-12-23T20:29:00.000-08:00

About this Tutorial

This tutorial presents three small sample applications using ADO. All three applications use a local MS Access database.

The first sample application introduces the ADO Data Control (ADODC) which demonstrates a "quick and dirty" way to connect to a remote database. The second and third applications use ADO code: the second allows navigation and searching of a database table; the third allows navigation and updating on a database table. All three connect to an ODBC Data Source, which must be set up through the Windows Control Panel. How to do this is described below.

Follow the steps below to set up an ODBC Data Source (this process is also called "setting up a DSN", where "DSN" stands for "Data Source Name"). These steps assume Windows 2000 for the operating system. On other versions of Windows, some steps may vary slightly.

· Via Windows Control Panel, double-click on Administrative Tools, then Data Sources (ODBC). The ODBC Data Source Administrator screen is displayed, as shown below. Click on the System DSN tab.

· Click the Add button. The Create New Data Source dialog box will appear. Select Microsoft Access Driver (*.mdb) from the list and click the Finish button.

· The ODBC Microsoft Access Setup dialog box will appear. For Data Source Name, type Biblio. If desired, you can type an entry for Description, but this is not required.

· Click the Select button. The Select Database dialog box appears. On a default installation of VB6 or Visual Studio 6, the BIBLIO.MDB sample database should reside in the folder C:\Program Files\Microsoft Visual Studio\VB98. Navigate to that folder, select BIBLIO.MDB from the file list, and click OK.

Note: If VB was installed in a different location on your system, navigate to the appropriate folder. If you do not have the BIBLIO.MDB sample database file on your system at all, you can download it here. In that case, copy the file to the folder of your choice, and navigate to that folder to select the database for this step.

· When you are returned to the ODBC Microsoft Access Setup screen, the database you selected should be reflected as shown below. Click OK to dismiss this screen.

· When you are returned to the ODBC Data Source Administrator screen, the new DSN should appear as shown below. Click OK to dismiss this screen.

At this point, the Biblio database is ready to be used with RDO in the sample application.

Sample Application 1: Using the ADO Data Control (ADODC)

To build the first sample application, follow the steps below.

· Start a new VB project, and from the Components dialog box (invoked from the Project -> Components menu), select Microsoft ADO Data Control 6.0 (SPx) as shown below and click OK.

The ADO Data Control should appear in your toolbox as shown below:

· Put an ADO Data Control on your form, and set the properties as follows:

Property	Value
Name	adoBiblio
DataSourceName	Biblio
SQL	select * from authors

· Now put three text boxes on the form, and set their Name, DataSource, and DataField properties as follows:

Name	DataSource	DataField
txtAuthor	adoBiblio	Author
txtAuID	adoBiblio	Au_ID
txtYearBorn	adoBiblio	Year Born

· Save and run the program. Notice how it works just like the other data control.

· Now change the SQL property of the data control to select * from authors order by author and run the program again. Notice the difference.

· Change the SQL property back to what it was and add three command buttons to the form, and set their Name and Caption properties as follows:

Name	Caption
cmdNameOrder	Order by Name
cmdYearOrder	Order by Year
cmdIDOrder	Order by ID

· Put the following code in the cmdNameOrder_Click event:

adoBiblio.SQL = "select * from authors order by author"

adoBiblio.Refresh

· Put the following code in the cmdYearOrder_Click event:

adoBiblio.SQL = "select * from authors order by [year born]"

adoBiblio.Refresh

· Put the following code in the cmdIDOrder_Click event:

adoBiblio.SQL = "select * from authors order by au_id"

adoBiblio.Refresh

· Save and run the program and see what happens when you click the buttons.

A screen-shot of the sample app at run-time is shown below:

Download the project files for this sample application here.

Sample Applications 2 and 3: Using ADO Code

Note: If you have previously downloaded and set up a DSN for the Property database as described in the previous topic on RDO, you can skip the set up steps below and resume here.

Sample applications 2 and 3 use a database called PROPERTY.MDB and can be downloaded here.

The Property database contains just one table called "Property". The columns of this table are defined as follows:

Column Name	Data Type	Notes
PROPNO	Number (Long Integer)	A number that uniquely identifies the property in the table. Should be treated as the Primary Key (although it is not defined as such in the sample database).
EMPNO	Number (Long Integer)	A number that identifies the real estate agent selling the property. In a real system, this would be the foreign key to the employee number in an Employee table (such a table is not present in the sample database).
ADDRESS	Text (20)	The street address of the property.
CITY	Text (15)	The city where the property is located.
STATE	Text (2)	The state where the property is located (2-character US state abbreviation).
ZIP	Text (5)	The zip code where the property is located.
NEIGHBORHOOD	Text (15)	The descriptive name of the neighborhood in which the property is located.
HOME_AGE	Number (Long Integer)	Age in years of the home. (A better table design choice would be to have this field be the date in which the property was built and have the application compute the age based on the current date.)
BEDS	Number (Long Integer)	Number of bedrooms in the property.
BATHS	Number (Single)	Number of bathrooms in the property (allows for a decimal value such as 2.5, indicating 2 ½ bathrooms – i.e. 2 full bathrooms and 1 "powder room").
FOOTAGE	Number (Long Integer)	The footage of the property.
ASKING	Number (Long Integer)	Asking price of the property in whole dollars.
BID	Number (Long Integer)	Bid amount of the potential buyer in whole dollars.
SALEPRICE	Number (Long Integer)	Sale price (amount the property actually sold for) in whole dollars.

Before coding or running sample application 2 or 3, you must set up an ODBC data source as was done for the previous sample application.

After downloading the file, move it to the folder of your choice. Then follow the exact same steps as before to set up the DSN, with these two exceptions:

(1) On the ODBC Microsoft Access Setup dialog box, type PropDB for the Data Source Name.

(2) In the Select Database dialog box, navigate to the location where you have placed the PROPERTY.MDB file.

Sample Application 2

To build Sample Application 2, start a new VB project and perform the following steps.

· From the Project -> References menu, check Microsoft ActiveX Data Objects 2.x (where x is the highest version that you have on your system) and click OK.

· This project uses the StatusBar control, so include the Microsoft Windows Common Controls 6.0 (SP6) from the Components dialog box, accessed from the Project -> Components menu.

· Create the form shown below. The names of the text boxes in the top frame are shown in the form. Set the Enabled property of the frame to False, which will automatically disable all of the textboxes within it, which is desired because this application does not allow updating of the data. The settings for the other controls are given below.

The navigation buttons have the following properties:

Name	Caption
cmdMoveFirst	<<
cmdMovePrevious	<
cmdMoveNext	>
cmdMoveLast	>>

The text box in the middle of the form has the following properties:

Name	txtCurrentQuery
MultiLine	True
Locked	True

The command buttons have the following properties:

Name	Caption	Enabled
cmdAllData	Reload All Records	True
cmdGetData	Run Query Now	False

In the "Select Criteria" frame:

The check boxes are an array:

Name	Caption
chkCriteria(0)	EmpNo
chkCriteria(1)	City
chkCriteria(2)	State

The labels are also an array:

Name	Caption	Enabled
lblCriteria(0)	=	False
lblCriteria(1)	Like	False
lblCriteria(2)	Like	False

The textboxes are also an array:

Name	Caption	Enabled
txtCriteria(0)	EmpNo	False
txtCriteria(1)	City	False
txtCriteria(2)	State	False

Place the StatusBar on the form and set its Style property to 1 – sbrSimple.

2. Code the General Declarations section as shown below. Here, two ADO objects, ADODB.Connection and ADODB.Recordset, are defined at the form level.

The ADODB.Connection object represents an open connection to a data source and a specific database on that data source, or an allocated but as yet unconnected object, which can be used to subsequently establish a connection.

The ADODB.Recordset object represents the rows that result from running a query,

Option Explicit

Dim mobjADOConn As ADODB.Connection

Dim mobjADORst As ADODB.Recordset

Dim mstrSQL As String

3. Code the Form_Load event. Here, the connection object variable mobjADOConn is made available for use by setting it to a new instance of ADODB.Connection. Then, the ConnectionString property and the Open method of the ADODB.Connection object are used.

The ConnectionString property takes a string with various arguments delimited by semicolons. When using a DSN as we are in this sample application, you typically need just the DSN name, the user id, and the password. The Open method then opens the connection to the database.

'-----------------------------------------------------------------------------

Private Sub Form_Load()

'-----------------------------------------------------------------------------

'set up the form and connect to the data source

On Error GoTo LocalError

'center the form:

Me.Top = (Screen.Height - Me.Height) / 2

Me.Left = (Screen.Width - Me.Width) / 2

' Connect to the Property database:

Set mobjADOConn = New ADODB.Connection

mobjADOConn.ConnectionString = "DSN=PropDB;Uid=admin;Pwd=;"

mobjADOConn.Open

Call cmdAllData_Click

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

4. Code the cmdAllData_Click event, which sets or resets the ADODB.Recordset object with a query to display all the rows in the table. The opening of the recordset takes place in the OpenNewRecordset subprocedure, called from this event procedure.

'-----------------------------------------------------------------------------

Private Sub cmdAllData_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

Dim lngX As Long

'select or reload the data to be displayed:

mstrSQL = "select * from property"

Call OpenNewRecordset

'load data into the text boxes

Call DataLoad

' reset the state of the search criteria controls

For lngX = 0 To 2

chkCriteria(lngX).Value = vbUnchecked

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

5. Create the user-defined subprocedure OpenNewRecordset.

Here, the recordset object mobjADORst is made available for use by setting (or resetting) it to a new instance of ADODB.Recordset.

The CursorLocation property is then set to the built-in constant adUseClient. The term "cursor" refers to the temporary rows of a recordset. The cursor location determines whether the cursor is stored on the client or the server, specified by the values adUseClient and adUseServer, respectively. Server-side cursor (adUseServer) is the default. There are tradeoffs using both types of cursors. Client-side cursors can take a long time to build because the data must be pulled over to the client, but once built, traversing the cursor is usually very fast. Client-side cursors often support more features than server-side cursors (the reason this sample application is using a client-side cursor is because we want to use AbsolutePosition property later, which only works with a client-side cursor). On the other hand, server-side cursors usually build faster but often support fewer features that client-side cursors.

The Open method of the recordset is then executed. The Open method has the following syntax:

RecordsetObject.Open Source, ActiveConnection, CursorType, LockType, Options

The Source argument is an optional variant that evaluates to a valid Command object, SQL statement, table name, stored procedure call, or filename of a persisted recordset.

The ActiveConnection argument is an optional variant that evaluates to a valid Connection object variable name or a string containing connection string parameters.

The CursorType argument is an optional value that determines the type of cursor that the provider should use when opening the recordset. The possible values and their descriptions are given below:

*Value*	*Description*
adOpenForwardOnly	(default) Used to open a forward-only cursor. Forward-only cursors create static snapshots of data. A recordset that uses a forward-only cursor is not directly updateable and can only be scrolled from beginning to end (i.e., "MoveNext" is the only "Move" method that can be used with this type of cursor). Forward-only cursors offer optimal performance in exchange for feature limitations. Forward-only cursors are sometimes referred to as firehose cursors.
adOpenStatic	Used to open a static cursor. A static cursor is a static copy of the data in the data source. Once created, no changes made by other users propagate to the recordset; the recordset never changes. Note: Client side cursors (like the one used in this sample application) use only adOpenStatic for CursorTypes regardless of which CursorType you select.
adOpenDynamic	Used to open a dynamic cursor. A dynamic cursor is a "live" recordset, meaning that any and all additions, changes, and deletions by other users affect the recordset. Dynamic-cursor recordsets support all types of navigation, including bookmarks (if bookmarks are supported by the provider). Dynamic cursors offer the most features of any cursor type, but at the expense of increased overhead.
adOpenKeyset	Used to open a keyset cursor. Keyset cursors are like dynamic cursors, except additions made by other users are not visible in the recordset. The recordset is affected by changes and deletions, however.

The LockType argument is an optional value that determines the type of locking that the provider should use when opening the recordset. The possible values and their descriptions are given below:

*Value*	*Description*
adLockReadOnly	(default) Specifies read-only locking. Records can be read, but data cannot be added, changed, or deleted. This is the locking method used with static cursors and forward-only cursors.
adLockPessimistic	Specifies pessimistic locking. The provider does what is necessary to ensure successful editing of records, usually by locking records at the data source immediately upon editing.
adLockOptimistic	Specifies optimistic locking. The provider locks records only when you call the Update method, not when you start editing.
adLockBatchOptimistic	Specifies optimistic batch locking. Records are locked in batch update mode, as opposed to immediate update mode. This option is required for client-side cursors.

The Options argument is an optional Long value that indicates how the Source should be evaluated. The possible values and their descriptions are given below:

*Value*	*Description*
adCmdText	Indicates that the provider should evaluate CommandText as a textual definition of a command. This options is used SQL statements.
adCmdTable	Indicates that the provider should evaluate CommandText as a table.
adCmdStoredProc	Indicates that the provider should evaluate CommandText as a stored procedure.
adCmdUnknown	Indicates that the type of command in the CommandText argument is not known and that the provider should attempt to interpret it. Typically results in poor performance.
adExecuteAsync	Indicates that the command should execute asynchronously.
adFetchAsync	Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be fetched asynchronously.

'-----------------------------------------------------------------------------

Private Sub OpenNewRecordset()

'-----------------------------------------------------------------------------

Set mobjADORst = New ADODB.Recordset

mobjADORst.CursorLocation = adUseClient

mobjADORst.Open mstrSQL, mobjADOConn, adOpenStatic, , adCmdText

' display current query

txtCurrentQuery.Text = mstrSQL

End Sub

6. Create the user-defined subprocedure DataLoad. This subprocedure gets the data from the recordset and puts each field into a text box. Data from the recordset is accessed via the Fields collection.

The Fields collection in ADO works identically to the Fields collection in DAO. A field can be referenced with or without specifying Fields, either by the field name in quotes or by its ordinal position in the resultset. The field can also be referenced with the bang (!) operator. All of the following would be valid ways of referencing the field "propno":

mobjADORst.Fields("propno")

mobjADORst ("propno")

mobjADORst.Fields(0)

mobjADORst(0)

mobjADORst!propno

'-----------------------------------------------------------------------------

Private Sub DataLoad()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

'copy the data from the recordset to the text boxes:

txtPropNo.Text = mobjADORst.Fields("propno")

txtEmpNo.Text = mobjADORst.Fields("empno")

txtAddress.Text = mobjADORst.Fields("address")

txtCity.Text = mobjADORst.Fields("city")

txtState.Text = mobjADORst.Fields("state")

txtZip.Text = mobjADORst.Fields("zip")

Call SetRecNum

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

7. Create the user-defined subprocedure SetRecNum. This sub displays the number of the current record at the bottom of the screen. The AbsolutePosition and RecordCount properties of the Recordset are used here.

The AbsolutePosition property specifies the current row in a recordset. Note: For AbsolutePosition to return a valid value with Access (Jet) databases (like the one used in the sample application), the CursorLocation must be set to adUseClient. An invalid value (-1) will be returned if adUseClient is specified.

The RecordCount property the total number of rows in the recordset. Note: RecordCount will not return a valid value with all cursor types (for example, RecordCount will return -1 with a forward-only cursor.) To ensure a valid RecordCount value, use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor.

'-----------------------------------------------------------------------------

Private Sub SetRecNum()

'-----------------------------------------------------------------------------

StatusBar1.SimpleText = "row " & mobjADORst.AbsolutePosition _

& " of " & mobjADORst.RecordCount

End Sub

8. Code the events for the navigation buttons as shown below, using the recordset "Move" methods to move to the first, last, next, or previous record, respectively.

'-----------------------------------------------------------------------------

Private Sub cmdMoveFirst_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MoveFirst

Call DataLoad

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMoveLast_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MoveLast

Call DataLoad

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMoveNext_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MoveNext

If mobjADORst.EOF Then

Beep

mobjADORst.MoveLast

End If

Call DataLoad

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMovePrevious_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MovePrevious

If mobjADORst.BOF Then

Beep

mobjADORst.MoveFirst

End If

Call DataLoad

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

9. When one of the check boxes is clicked, the label and text box next to it should be enabled (or disabled, if clicking the check box unchecks it). Note also that the cmdGetData button (the one with the "Run Query Now" caption) should only be enabled if one of the checkboxes is checked.

'-----------------------------------------------------------------------------

Private Sub chkCriteria_Click(Index As Integer)

'-----------------------------------------------------------------------------

' disable the 'Run Query Now' button

cmdGetData.Enabled = False

'when the user clicks on a check box, enable the label and text

'box that go with it.

If chkCriteria(Index).Value = vbChecked Then

txtCriteria(Index).Enabled = True

lblCriteria(Index).Enabled = True

txtCriteria(Index).SetFocus

txtCriteria(Index).SelStart = 0

txtCriteria(Index).SelLength = Len(txtCriteria(Index).Text)

' enable the 'Run Query Now' button only if a box is checked.

cmdGetData.Enabled = True

Else

txtCriteria(Index).Enabled = False

lblCriteria(Index).Enabled = False

End If

End Sub

10. After the user has selected which fields to use and entered values in the text boxes, they click the cmdGetData button to create a new recordset with new data. Note that if the user selects (checks) a field, but does not enter search criteria in the corresponding textbox, an error message is generated and the query is not run.

'-----------------------------------------------------------------------------

Private Sub cmdGetData_Click()

'-----------------------------------------------------------------------------

'run the query that the user has created

On Error GoTo LocalError

Dim blnFirstOne As Boolean

blnFirstOne = True

mstrSQL = "select * from property where "

If chkCriteria(0).Value = vbChecked Then

If (txtCriteria(0).Text = "") Or (Not IsNumeric(txtCriteria(0).Text)) Then

MsgBox "Employee number is missing or non-numeric. Query not run.", _

vbExclamation, _

"ADO Example"

Exit Sub

End If

blnFirstOne = False

mstrSQL = mstrSQL & "empno = " & txtCriteria(0).Text

End If

If chkCriteria(1).Value = vbChecked Then

If txtCriteria(1).Text = "" Then

MsgBox "City criteria is missing. Query not run.", _

vbExclamation, _

"ADO Example"

Exit Sub

End If

If blnFirstOne = False Then

mstrSQL = mstrSQL & " and"

End If

blnFirstOne = False

mstrSQL = mstrSQL & " city like '" & txtCriteria(1).Text & "'"

End If

If chkCriteria(2).Value = vbChecked Then

If txtCriteria(2).Text = "" Then

MsgBox "State criteria is missing. Query not run.", _

vbExclamation, _

"ADO Example"

Exit Sub

End If

If blnFirstOne = False Then

mstrSQL = mstrSQL & " and"

End If

blnFirstOne = False

mstrSQL = mstrSQL & " state like '" & txtCriteria(2).Text & "'"

End If

OpenNewRecordset

'make sure that the query did not return 0 rows:

If mobjADORst.EOF Then

MsgBox "Your query (" & mstrSQL & ") returned no records! " _

& "The default query to return all records will now be rerun.", _

vbExclamation, _

"ADO Example"

'reload the form with all the records

cmdAllData_Click

Else

MsgBox "Your query returned " & mobjADORst.RecordCount & " records.", _

vbInformation, _

"ADO Example"

'load data into the text boxes

Call DataLoad

End If

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

11. Save and run. Note: When entering the "Like" criteria for City and/or State, you can use the wildcard character % to represent any number of characters and the wildcard character _ (underscore) the represent a single character. For example, entering "M%" for the City criteria would return all rows where the city field begins with the letter "M".

Download the project files for this sample application here.

Sample Application 3

Sample Application 3 demonstrates how to add, update, and delete records with ADO.

When the application is first run, the user is prompted to enter a minimum asking price to possibly limit the number of records they want to work with (i.e., "I only want to work with properties that are selling for $200,000 or more). If the user wants to work with all properties, they would simply accept the default of 0 from the prompt. If the user clicks the Cancel button, the application will end.

Once the user has entered the minimum asking price, the main screen of the application is displayed. Initially, the screen is in "browse" mode, where the user can use the navigation buttons to move to the first, previous, next or last record. The data cannot be edited in this mode. If they want to initiate an add or an update, delete a record, or exit the application, they may do so via the appropriate button. Saving or cancelling is not applicable in this mode, so those buttons are disabled.

If the user clicks the Add button, the fields on the screen are enabled and cleared, and the user can enter the information for the new property. All buttons except Save and Cancel are now disabled. After the user has made entries in the fields, he or she would click Save to add the new record to the database table, or, if they changed their mind, would click Cancel to discard the new record. In either case (clicking Save or Cancel) the user is returned to browse mode. When Save is clicked, the application validates the entries and will only save the record if all fields pass edit (otherwise, a message will appear indicating the problem entry and focus will be set to the problem field).

If the user clicks the Update button, the fields on the screen are enabled and the user can modify any or all of the fields (except for the Property Number, which is the primary key of the table). All buttons except Save and Cancel are now disabled. After the user has made modifications in the desired fields, he or she would click Save to update the record to the database table, or, if they changed their mind, would click Cancel to discard the changes. In either case (clicking Save or Cancel) the user is returned to browse mode. When Save is clicked, the application validates the entries and will only save the record if all fields pass edit (otherwise, a message will appear indicating the problem entry and focus will be set to the problem field).

If the user clicks the Delete button, the user is asked to confirm that they want to delete the current record. If they respond Yes, the record is deleted from the database table, and the main screen shows the next record in the table.

To build Sample Application 3, start a new VB project and perform the following steps.

· From the Project -> References menu, check Microsoft ActiveX Data Objects 2.x Library and click OK.

· This project uses the StatusBar control, so include the Microsoft Windows Common Controls 6.0 (SP6) from the Components dialog box, accessed from the Project -> Components menu. Check this item and click OK.

· Create the form shown below. The settings for the various controls are given below.

· There are nine textboxes in the main frame of the form. The names and MaxLength settings for these are given below:

Name	Properties
txtPropNo	MaxLength: 5
txtEmpNo	MaxLength: 4
txtAddress	MaxLength: 20
txtCity	MaxLength: 15
txtState	MaxLength: 2
txtZip	MaxLength: 5
txtBeds	MaxLength: 1
txtBaths	MaxLength: 3 (allows fractional amount, like 1.5)
txtAsking	MaxLength: 0 (not specified)

· Set up the Command Buttons as follows:

Name	Caption
cmdMoveFirst	<<
cmdMovePrevious	<
cmdMoveNext	>
cmdMoveLast	>>
cmdAdd	Add
cmdUpdate	Update
cmdDelete	Delete
cmdSave	Save
cmdCancel	Cancel
cmdExit	Exit

· All controls on your form should have their TabIndex property set such that the tabbing order is correct.

· Add a Module to the project, name it modCommon, and enter the code shown below. The code contains procedures described as follows:

CenterForm	Sub to center a form on the screen
ValidKey	Function to validate a keystroke for use in the KeyPress event of a textbox
ConvertUpper	Function to convert an alphabetic character entered in a textbox to uppercase, used in the KeyPress event of a textbox
SelectTextBoxText	Sub to highlight the text of a textbox when it receives focus. Used in the GotFocus event of a textbox.
TabToNextTextBox	Sub to "autotab" from one textbox to another when maximum number of characters that can be entered into the first textbox has been reached.
UnFormatNumber	Function to strip out non-numeric characters (dollar signs, commas, etc.) from a formatted number.

Option Explicit

Public Const gstrNUMERIC_DIGITS As String = "0123456789"

Public Const gstrUPPER_ALPHA_PLUS As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ,'-"

Public gblnPopulating As Boolean

'------------------------------------------------------------------------

Public Sub CenterForm(pobjForm As Form)

'------------------------------------------------------------------------

With pobjForm

.Top = (Screen.Height - .Height) / 2

.Left = (Screen.Width - .Width) / 2

End With

End Sub

'------------------------------------------------------------------------

Public Function ValidKey(pintKeyValue As Integer, _

pstrSearchString As String) As Integer

'------------------------------------------------------------------------

' Common function to filter out keyboard characters passed to this

' function from KeyPress events.

' Typical call:

' KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)

If pintKeyValue < 32 _

Or InStr(pstrSearchString, Chr$(pintKeyValue)) > 0 Then

'Do nothing - i.e., accept the control character or any key

' in the search string passed to this function ...

Else

'cancel (do not accept) any other key ...

pintKeyValue = 0

End If

ValidKey = pintKeyValue

End Function

'------------------------------------------------------------------------

Public Function ConvertUpper(pintKeyValue As Integer) As Integer

'------------------------------------------------------------------------

' Common function to force alphabetic keyboard characters to uppercase

' when called from the KeyPress event.

' Typical call:

' KeyAscii = ConvertUpper(KeyAscii)

If Chr$(pintKeyValue) >= "a" And Chr$(pintKeyValue) <= "z" Then

pintKeyValue = pintKeyValue - 32

End If

ConvertUpper = pintKeyValue

End Function

'-----------------------------------------------------------------------------

Public Sub SelectTextBoxText(pobjTextbox As TextBox)

'-----------------------------------------------------------------------------

With pobjTextbox

.SelStart = 0

.SelLength = Len(.Text)

End With

End Sub

'-----------------------------------------------------------------------------

Public Sub TabToNextTextBox(pobjTextBox1 As TextBox, pobjTextBox2 As TextBox)

'-----------------------------------------------------------------------------

If gblnPopulating Then Exit Sub

If pobjTextBox2.Enabled = False Then Exit Sub

If Len(pobjTextBox1.Text) = pobjTextBox1.MaxLength Then

pobjTextBox2.SetFocus

End If

End Sub

'-----------------------------------------------------------------------------

Public Function UnFormatNumber(pstrNumberIn As String) As String

'-----------------------------------------------------------------------------

Dim lngX As Long

Dim strCurrChar As String

Dim strNumberOut As String

strNumberOut = ""

For lngX = 1 To Len(pstrNumberIn)

strCurrChar = Mid$(pstrNumberIn, lngX, 1)

If InStr("0123456789.", strCurrChar) > 0 Then

strNumberOut = strNumberOut & strCurrChar

End If

UnFormatNumber = strNumberOut

End Function

· Code the General Declarations section as shown below. Here, as in the previous sample application, two ADO object variables, mobjADOConn and mobjADORst, are defined at the form level, as are some other form-level variables that will be needed.

Option Explicit

Dim mobjADOConn As ADODB.Connection

Dim mobjADORst As ADODB.Recordset

Private mstrSQL As String

Private mdblMinAsking As Double

Private mblnUpdatePending As Boolean

Private mstrUpdateType As String

Private mavntUSStates As Variant

· Code the Form_Load event as shown below. In it, a programmer-defined Sub named GetMinimumAsking is called (that routine is the one that displays the initial prompt to the user to enter the minimum asking price of the properties they want to work with). Then, the variant array mavntUSStates is loaded with the 50 US state abbreviations, needed for validating the state input by the user. This is followed by a call to the CenterForm sub. Then, the ADO connection object (mobjADOConn) is instantiated, its ConnectionString property is set, and the Open method is invoked so that we can use the Property database in the application. This is followed by a call to the programmer-defined Sub GetPropertyData (which runs the query to create the recordset that will be used to browse the Property table records), followed by a call to the programmer-defined Sub SetFormState (which enables and disables controls at the appropriate time).

'-----------------------------------------------------------------------------

Private Sub Form_Load()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

' obtain the minimum asking price for the properties to be worked with

GetMinimumAsking

' load the array of states to be used for validation

mavntUSStates = Array("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DC", _

"DE", "FL", "GA", "HI", "IA", "ID", "IL", "IN", _

"KS", "KY", "LA", "MA", "MD", "ME", "MI", "MN", _

"MO", "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _

"NM", "NV", "NY", "OH", "OK", "OR", "PA", "RI", _

"SC", "SD", "TN", "TX", "UT", "VA", "VT", "WA", _

"WI", "WV", "WY")

'center the form:

CenterForm Me

' Connect to the Property database:

Set mobjADOConn = New ADODB.Connection

mobjADOConn.ConnectionString = "DSN=PropDB;Uid=admin;Pwd=;"

mobjADOConn.Open

Call GetPropertyData

SetFormState False

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

Code the GetMinimumAsking Sub, which uses the InputBox function to prompt to the user to enter the minimum asking price of the properties they want to work with. The resulting value is then stored in the form-level variable mdblMinAsking.

'-----------------------------------------------------------------------------

Private Sub GetMinimumAsking()

'-----------------------------------------------------------------------------

Dim strInputBoxPrompt As String

Dim strAsking As String

strInputBoxPrompt = "Enter the minimum asking price (for example, 200000) " _

& "for the properties that you want to work with this session." _

& vbNewLine _

& "To work with ALL properties, leave the default of zero."

strAsking = InputBox(strInputBoxPrompt, "Minimum Asking Price", "0")

If strAsking = "" Then

' user clicked Cancel button on the input box, so end the app

End

End If

mdblMinAsking = Val(strAsking)

End Sub

Code the GetPropertyData Sub, which builds the SQL to get the property records meeting the minimum asking price condition. The Recordset object is then instantiated, its CursorLocation property is set, and its Open method is invoked to execute the SQL and return the resultset. This is done in a loop in case the resultset does not return any records due to the fact no records in the table met the asking price condition. In that situation, the user is given the opportunity to specify a different asking price value. Following this, the programmer-defined Sub PopulateFormFields is called (which displays the fields from the current record in their corresponding textboxes on the form).

'-----------------------------------------------------------------------------

Private Sub GetPropertyData()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

Dim blnGotData As Boolean

blnGotData = False

'select or reload the data to be displayed:

mstrSQL = "select propno" _

& " , empno" _

& " , address" _

& " , city" _

& " , state" _

& " , zip" _

& " , beds" _

& " , baths" _

& " , asking" _

& " from property" _

& " where asking >= " & mdblMinAsking _

& " order by propno"

Set mobjADORst = New ADODB.Recordset

mobjADORst.CursorLocation = adUseClient

mobjADORst.Open mstrSQL, mobjADOConn, adOpenDynamic, adLockOptimistic, adCmdText

If mobjADORst.EOF Then

If MsgBox("There are no properties with an asking price >= " _

& Format$(mdblMinAsking, "Currency") _

& ". Do you want to try again with a different value?", _

vbYesNo + vbQuestion, _

"Asking Price") _

= vbYes Then

GetMinimumAsking

Else

End

End If

Else

blnGotData = True

End If

Loop Until blnGotData

'load data into the text boxes

Call PopulateFormFields

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

Code the PopulateFormFields Sub, which assigns the fields from the current record to their corresponding textboxes on the form. Note that the gblnPopulating Boolean variable is set to True prior to the assignments and set to False after the assignments. This value is used to control whether or not certain code executes in the event procedures for some of these textboxes. The Sub SetRecNum is then called.

'-----------------------------------------------------------------------------

Private Sub PopulateFormFields()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

gblnPopulating = True

'copy the data from the resultset to the text boxes:

txtPropNo.Text = mobjADORst.Fields("propno")

txtEmpNo.Text = mobjADORst.Fields("empno")

txtAddress.Text = mobjADORst.Fields("address")

txtCity.Text = mobjADORst.Fields("city")

txtState.Text = mobjADORst.Fields("state")

txtZip.Text = mobjADORst.Fields("zip")

txtBeds.Text = mobjADORst.Fields("beds")

txtBaths.Text = mobjADORst.Fields("baths")

txtAsking.Text = Format$(mobjADORst.Fields("asking"), "Currency")

gblnPopulating = False

Call SetRecNum

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

Code the SetRecNum Sub. This sub is identical to the one used in Sample Application 2. It displays the number of the current record at the bottom of the screen using the AbsolutePosition and RowCount properties of the Recordset object.

'-----------------------------------------------------------------------------

Private Sub SetRecNum()

'-----------------------------------------------------------------------------

StatusBar1.SimpleText = "row " & mobjADORst.AbsolutePosition _

& " of " & mobjADORst.RecordCount

End Sub

Code the SetFormState Sub, which takes in a Boolean argument used to set the Enabled property of the controls on the form. Based on whether the value True or False is passed to this sub, this sub ensures that the textboxes are enabled for adds and updates and disabled for browsing; it also ensures that the various command buttons are enabled or disabled at the appropriate time. This Sub also sets the form-level Boolean variable mblnUpdatePending.

'-----------------------------------------------------------------------------

Private Sub SetFormState(pblnEnabled As Boolean)

'-----------------------------------------------------------------------------

txtPropNo.Enabled = pblnEnabled

txtEmpNo.Enabled = pblnEnabled

txtAddress.Enabled = pblnEnabled

txtCity.Enabled = pblnEnabled

txtState.Enabled = pblnEnabled

txtZip.Enabled = pblnEnabled

txtBeds.Enabled = pblnEnabled

txtBaths.Enabled = pblnEnabled

txtAsking.Enabled = pblnEnabled

cmdSave.Enabled = pblnEnabled

cmdCancel.Enabled = pblnEnabled

cmdAdd.Enabled = Not pblnEnabled

cmdUpdate.Enabled = Not pblnEnabled

cmdDelete.Enabled = Not pblnEnabled

cmdExit.Enabled = Not pblnEnabled

cmdMoveFirst.Enabled = Not pblnEnabled

cmdMoveNext.Enabled = Not pblnEnabled

cmdMovePrevious.Enabled = Not pblnEnabled

cmdMoveLast.Enabled = Not pblnEnabled

mblnUpdatePending = pblnEnabled

End Sub

Code the Form_Unload event. In it, the form-level Boolean variable mblnUpdatePending is tested to see if (well, an update is pending – i.e., whether an add or update is in progress). If the user is in the middle of an add or update and then clicks the "X" button on the upper-right corner of the form, they will receive the message that they must save or cancel prior to exiting the application, and the form will NOT be unloaded (because we are assigning a non-zero value to the Cancel argument in that situation). Provided that an add or update is not in progress, we set the database objects to Nothing and the Unload will complete.

'-----------------------------------------------------------------------------

Private Sub Form_Unload(Cancel As Integer)

'-----------------------------------------------------------------------------

If mblnUpdatePending Then

MsgBox "You must save or cancel the current operation prior to exiting.", _

vbExclamation, _

"Exit"

Cancel = 1

Else

Set mobjADORst = Nothing

Set mobjADOConn = Nothing

End If

End Sub

Code the events for the various Textboxes as shown below. The code in these events ensure the following:

· For all, highlight the text in the textbox when it receives focus.

· For all but the last textbox, if the maximum number of characters typed into the textbox is reached, auto-tab to the next textbox.

· Only numeric digits should be entered into the property number, employee number, zip codes, and beds textboxes.

· Only numeric digits and optionally one decimal point should be entered into the baths and asking textboxes.

· Force uppercase on the state textbox.

· When the asking textbox receives focus, the value in there should be unformatted. When the asking textbox loses focus, its value should be formatted as currency.

'-----------------------------------------------------------------------------

' Textbox events

'-----------------------------------------------------------------------------

' property #

Private Sub txtPropNo_GotFocus()

SelectTextBoxText txtPropNo

End Sub

Private Sub txtPropNo_KeyPress(KeyAscii As Integer)

KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)

End Sub

Private Sub txtPropNo_Change()

TabToNextTextBox txtPropNo, txtEmpNo

End Sub

' emp #

Private Sub txtEmpNo_GotFocus()

SelectTextBoxText txtEmpNo

End Sub

Private Sub txtEmpNo_KeyPress(KeyAscii As Integer)

KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)

End Sub

Private Sub txtEmpNo_Change()

TabToNextTextBox txtEmpNo, txtAddress

End Sub

' address

Private Sub txtAddress_GotFocus()

SelectTextBoxText txtAddress

End Sub

Private Sub txtAddress_Change()

TabToNextTextBox txtAddress, txtCity

End Sub

' city

Private Sub txtCity_GotFocus()

SelectTextBoxText txtCity

End Sub

Private Sub txtCity_Change()

TabToNextTextBox txtCity, txtState

End Sub

' state

Private Sub txtState_GotFocus()

SelectTextBoxText txtState

End Sub

Private Sub txtState_KeyPress(KeyAscii As Integer)

KeyAscii = ConvertUpper(KeyAscii)

End Sub

Private Sub txtState_Change()

TabToNextTextBox txtState, txtZip

End Sub

' zip

Private Sub txtZip_GotFocus()

SelectTextBoxText txtZip

End Sub

Private Sub txtZip_KeyPress(KeyAscii As Integer)

KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)

End Sub

Private Sub txtZip_Change()

TabToNextTextBox txtZip, txtBeds

End Sub

' beds

Private Sub txtBeds_GotFocus()

SelectTextBoxText txtBeds

End Sub

Private Sub txtBeds_KeyPress(KeyAscii As Integer)

KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)

End Sub

Private Sub txtBeds_Change()

TabToNextTextBox txtBeds, txtBaths

End Sub

' baths

Private Sub txtBaths_GotFocus()

SelectTextBoxText txtBaths

End Sub

Private Sub txtBaths_KeyPress(KeyAscii As Integer)

KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS & ".")

' if text already has a decimal point, do not allow another ...

If Chr$(KeyAscii) = "." And InStr(txtBaths.Text, ".") > 0 Then

KeyAscii = 0

End If

End Sub

Private Sub txtBaths_Change()

TabToNextTextBox txtBaths, txtAsking

End Sub

' asking price

Private Sub txtAsking_GotFocus()

txtAsking.Text = UnFormatNumber(txtAsking.Text)

SelectTextBoxText txtAsking

End Sub

Private Sub txtAsking_KeyPress(KeyAscii As Integer)

KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS & ".")

' if text already has a decimal point, do not allow another ...

If Chr$(KeyAscii) = "." And InStr(txtAsking.Text, ".") > 0 Then

KeyAscii = 0

End If

End Sub

Private Sub txtAsking_LostFocus()

txtAsking.Text = Format$(txtAsking.Text, "Currency")

End Sub

Code the events for the navigation buttons as shown below, using the resultset "Move" methods to move to the first, last, next, or previous record, respectively.

'-----------------------------------------------------------------------------

Private Sub cmdMoveFirst_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MoveFirst

Call PopulateFormFields

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMoveLast_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MoveLast

Call PopulateFormFields

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMoveNext_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MoveNext

If mobjADORst.EOF Then

Beep

mobjADORst.MoveLast

End If

Call PopulateFormFields

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMovePrevious_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

mobjADORst.MovePrevious

If mobjADORst.BOF Then

Beep

mobjADORst.MoveFirst

End If

Call PopulateFormFields

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

Code the Click event for the cmdAdd button. In it, the textboxes are cleared, the SetFormState sub is called (passing it a parameter of True, which will enable the textboxes and the Save and Cancel buttons and disable all the other buttons), set the form-level variable mstrUpdateType to "A" (indicating that an add is pending) and sets focus to the Property Number field.

'-----------------------------------------------------------------------------

Private Sub cmdAdd_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

'clear all the text boxes:

txtPropNo.Text = ""

txtEmpNo.Text = ""

txtAddress.Text = ""

txtCity.Text = ""

txtState.Text = ""

txtZip.Text = ""

txtBeds.Text = ""

txtBaths.Text = ""

txtAsking.Text = ""

SetFormState True

mstrUpdateType = "A"

txtPropNo.SetFocus

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

Code the Click event for the cmdUpdate button. In it, the SetFormState sub is called (passing it a parameter of True, which will enable the textboxes and the Save and Cancel buttons and disable all the other buttons), set the form-level variable mstrUpdateType to "U" (indicating that an update is pending), disables the Property Number field (because it is the primary key and should not be changed) and sets focus to the Employee Number field.

'-----------------------------------------------------------------------------

Private Sub cmdUpdate_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

SetFormState True

mstrUpdateType = "U"

' being that propno is the primary key, it should not be updatable

txtPropNo.Enabled = False

txtEmpNo.SetFocus

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

Code the Click event for the cmdSave button. The user would click this button after they have completed entries for an add or update. This sub first invokes the ValidateAllFields function, which returns a Boolean indicating whether or not all entries passed their edit checks. If not, we exit the sub and the record is not saved; the user remains in "update pending" mode and has the opportunity to correct the entries. Provided that validation is successful, the sub proceeds. The mstrUpdateType variable is checked to see whether we are dealing with an add or an update.

If we are dealing with an add, we invoke the AddNew method of the Recordset object. The AddNew method prepares a new row you can edit and subsequently add to the Recordset object using the Update method. After you modify the new row, you must use the Update method to save the changes and add the row to the result set. No changes are made to the database until you use the Update method. (The Update method is invoked after the content of the textboxes has been assigned to the database fields.)

If we are dealing with an update, we can just start modifying the fields (provided an appropriate cursor type has been selected) – unlike DAO and RDO, ADO does not use an Edit method. Changes made to the current row’s columns are copied to the copy buffer. After you make the desired changes to the row, use the Update method to save your changes or the CancelUpdate method to discard them. (If you move on to another record without invoking Update, your changes will be lost.)

The content of the textboxes is assigned to the database fields, then the Update method is invoked. The Update method saves the contents of the copy buffer row to a specified updatable Recordset object and discards the copy buffer.

The mstrUpdateType variable is checked once again, and if we are dealing with an add, there is some extra work to do. Although the new record has been added, the original resultset still does not contain the new record. The Requery method must be invoked, which updates the data in a Recordset object by re-executing the query on which the object is based. The Find method is then used to position to the new record. The ADO Find method has the following syntax:

RecordsetObject.Find Criteria, SkipRows, SearchDirection, Start

The Criteria argument is a String value that specifies the column name, comparison operator, and value to use in the search. Only a single-column name may be specified in criteria; multi-column searches are not supported. The comparison operator may be ">" (greater than), "<" (less than), "=" (equal), ">=" (greater than or equal), "<=" (less than or equal), "<>" (not equal), or "like" (pattern matching). The value may be a string, floating-point number, or date. String values are delimited with single quotes or "#" (number sign) marks (for example, "state = 'WA'" or "state = #WA#"). Date values are delimited with "#" (number sign) marks (for example, "start_date > #7/22/97#"). These values can contain hours, minutes, and seconds to indicate time stamps, but should not contain milliseconds or errors will occur. If the comparison operator is "like", the string value may contain an asterisk (*) to find one or more occurrences of any character or substring. For example, "state like 'M*'" matches Maine and Massachusetts. You can also use leading and trailing asterisks to find a substring contained within the values. For example, "state like '*as*'" matches Alaska, Arkansas, and Massachusetts. Asterisks can be used only at the end of a criteria string, or together at both the beginning and end of a criteria string, as shown above. You cannot use the asterisk as a leading wildcard ('*str'), or embedded wildcard ('s*r'). This will cause an error.

SkipRows is an optional Long value, whose default is zero, that specifies the row offset from the current row (or bookmark row specified by the Start argument, if present) to begin the search. By default, the search will start on the current row.

SearchDirection is an optional value that determines in which direction the search is performed. The value is specified by the constants adSearchForward (the default) or adSearchBackward, which equate to values of 1 or -1, respectively.

Start is an optional Variant bookmark that functions as the starting position for the search.

Note: Unlike DAO, ADO does not have a "NoMatch" property. If the ADO Find method is unsuccessful, the record pointer is positioned at the end of the Recordset.

The SetRecNum sub is then be called to display the status bar information about the new record. The SetFormState sub is then called with a parameter of False, which causes the textboxes and the Save and Cancel buttons to be disabled and all other buttons to be enabled.

Note that in the statement that assigns the contents of the txtAsking textbox to the asking field of the table, our UnFormatNumber function is used to strip off the non-numeric characters. This is because we are using a display format that includes a dollar sign and commas on the txtAsking control, and an error would occur if we attempted to assign this directly to the asking field, which is defined as numeric.

'-----------------------------------------------------------------------------

Private Sub cmdSave_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

If Not ValidateAllFields Then Exit Sub

If mstrUpdateType = "A" Then

mobjADORst.AddNew

Else

' We can just update the fields. No explicit Edit method

' is available or needed.

End If

'save the data to the database:

mobjADORst.Fields("propno") = txtPropNo.Text

mobjADORst.Fields("empno") = txtEmpNo.Text

mobjADORst.Fields("address") = txtAddress.Text

mobjADORst.Fields("city") = txtCity.Text

mobjADORst.Fields("state") = txtState.Text

mobjADORst.Fields("zip") = txtZip.Text

mobjADORst.Fields("beds") = txtBeds.Text

mobjADORst.Fields("baths") = txtBaths.Text

mobjADORst.Fields("asking") = UnFormatNumber(txtAsking.Text)

mobjADORst.Update

If mstrUpdateType = "A" Then

'after the new record is added, the db must be re-queried

'so that the resultset contains the new record:

mobjADORst.Requery

' reposition to the record just added

mobjADORst.Find "propno = " & txtPropNo.Text

'display status info about the new record

SetRecNum

End If

Reset:

SetFormState False

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

Resume Reset

End Sub

Code the Click event for the cmdDelete button. The user is first asked to confirm that they want to delete the record, and if so, the Delete method of the resultset object is invoked, which deletes the current row in an updatable resultset object. The Requery method is then invoked so that the record is removed from the resultset that the user is working with. The Find method is then invoked to position the next record after the deleted one. If it was the last record that was deleted, then we position to the "new" last record using the MoveLast property. PopulateFormFields must then be called to display the contents of the new current record.

'-----------------------------------------------------------------------------

Private Sub cmdDelete_Click()

'-----------------------------------------------------------------------------

On Error GoTo LocalError

'when the current record is deleted, the current location in the recordset

'is invalid. use the Requery method to re-execute the query and update

'the data.

If MsgBox("Are you sure you want to delete this record?", _

vbYesNo + vbQuestion, _

"Delete") = vbNo Then

Exit Sub

End If

mobjADORst.Delete

mobjADORst.Requery

' reposition to one past the record just deleted

mobjADORst.Find "propno > " & txtPropNo.Text

' If it was the last record that was deleted, the Find method will

' come back with EOF, in which case we should MoveLast to position

' us to the "new" last record ...

If mobjADORst.EOF Then mobjADORst.MoveLast

'load data into the text boxes:

Call PopulateFormFields

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

The ValidateAllFields function, which returns a Boolean value indicating whether or not all fields have passed validation checks. This function calls upon two "helper" functions: PropertyExists and ValidState. When the user is doing an add, the PropertyExist function is called to see whether or not the proposed Property Number is already being used in the Property table. If so, the user is informed that they can't use that number (because it is the primary key and must be unique) and so they must use a different number. The ValidState routine is called to ensure that the user has entered a valid US state. The code for all three functions is shown below.

'-----------------------------------------------------------------------------

Private Function ValidateAllFields() As Boolean

'-----------------------------------------------------------------------------

ValidateAllFields = False 'guilty until proven innocent

If mstrUpdateType = "A" Then

If txtPropNo.Text = "" Then

MsgBox "Property # must not be blank.", vbExclamation, "Property #"

txtPropNo.SetFocus

Exit Function

ElseIf PropertyExists Then

MsgBox "Property # already exists. Please use a different #.", _

vbExclamation, _

"Property #"

txtPropNo.SetFocus

Exit Function

End If

If txtEmpNo.Text = "" Then

MsgBox "Emp # must not be blank.", vbExclamation, "Emp #"

txtEmpNo.SetFocus

Exit Function

End If

If txtAddress.Text = "" Then

MsgBox "Address must not be blank.", vbExclamation, "Address"

txtAddress.SetFocus

Exit Function

End If

If txtCity.Text = "" Then

MsgBox "City must not be blank.", vbExclamation, "City"

txtCity.SetFocus

Exit Function

End If

If Not ValidState Then

MsgBox "Missing or invalid state.", vbExclamation, "State"

txtState.SetFocus

Exit Function

End If

If txtZip.Text = "" Or Len(txtZip.Text) = 5 Then

' it's OK

Else

MsgBox "Zip code must either be blank or exactly 5 digits.", _

vbExclamation, _

"Zip Code"

txtZip.SetFocus

Exit Function

End If

If Val(txtBeds.Text) = 0 Then

MsgBox "Beds must not be zero.", vbExclamation, "Beds"

txtBeds.SetFocus

Exit Function

End If

If Val(txtBaths.Text) = 0 Then

MsgBox "Baths must not be zero.", vbExclamation, "Baths"

txtBaths.SetFocus

Exit Function

End If

If Val(UnFormatNumber(txtAsking.Text)) = 0 Then

MsgBox "Asking must not be zero.", vbExclamation, "Asking"

txtAsking.SetFocus

Exit Function

End If

' if we make it here, all fields have passed edit

ValidateAllFields = True

End Function

'--------------------------------------------------------------------------------

Private Function ValidState() As Boolean

'--------------------------------------------------------------------------------

Dim lngX As Long

Dim blnStateFound As Boolean

blnStateFound = False

For lngX = 0 To UBound(mavntUSStates)

If txtState.Text = mavntUSStates(lngX) Then

blnStateFound = True

Exit For

End If

ValidState = blnStateFound

End Function

'-----------------------------------------------------------------------------

Private Function PropertyExists() As Boolean

'-----------------------------------------------------------------------------

Dim objTempRst As New ADODB.Recordset

Dim strSQL As String

strSQL = "select count(*) as the_count from property where propno = " & txtPropNo.Text

objTempRst.Open strSQL, mobjADOConn, adOpenForwardOnly, , adCmdText

If objTempRst("the_count") > 0 Then

PropertyExists = True

Else

PropertyExists = False

End If

End Function

Creating PDF files in Visual Basic

2011-12-23T20:25:00.000-08:00

In the previous tutorial we looked out how to create a simple PDF document that was one page and was only text. If you've seen many PDF documents on the web or elsewhere you know that they usually include more than just text. Fortunately we are able to do these things as well. In this VB PDF tutorial we're going to look at how to further use the mjwPDF class accomplish what we need. By the end of the tutorial you should be able to create a multi-page document that has headers, footers with page numbers, shapes, graphics, and web links.If you want, you can download the source code for this VB PDF tutorial and follow along with it.
If you haven't read the introductory tutorial about Creating a PDF document using Visual Basic. Please do so first. This VB tutorial builds off the previous one. In fact to start with lets look at the code we created before:

Dim objPDF As New mjwPDF
    
    ' Set the PDF title and filename
    objPDF.PDFTitle = "Test PDF Document"
    objPDF.PDFFileName = App.Path & "\test.pdf"
    
    ' We must tell the class where the PDF fonts are located
    objPDF.PDFLoadAfm = App.Path & "\Fonts"
    
    ' View the PDF file after we create it
    objPDF.PDFView = True
    
    ' Begin our PDF document
    objPDF.PDFBeginDoc
        ' Set the font name, size, and style
        objPDF.PDFSetFont FONT_ARIAL, 15, FONT_BOLD
        
        ' Set the text color
        objPDF.PDFSetTextColor = vbBlue
        
        ' Set the text we want to print
        objPDF.PDFTextOut "Hello, World! From mjwPDF (www.vb6.us)"
    
    ' End our PDF document (this will save it to the filename)
    objPDF.PDFEndDoc
End Sub

Great. With this code we've done all the initializing and created a basic file. Now lets add some formatting options. Right after we set the fonts folder lets add a few lines of code that tell mjwPDF what the document layout should be like.

' We must tell the class where the PDF fonts are located
    objPDF.PDFLoadAfm = App.Path & "\Fonts"
    
    ' Set the file properties
    objPDF.PDFSetLayoutMode = LAYOUT_DEFAULT
    objPDF.PDFFormatPage = FORMAT_A4
    objPDF.PDFOrientation = ORIENT_PORTRAIT
    objPDF.PDFSetUnit = UNIT_PT
    
    ' View the PDF file after we create it
    objPDF.PDFView = True

This code sets up a standard page (letter size) in portrait orientation and our units of measure as points.
Next lets do something fun. Often times you want to add a heading to a PDF document such as "Very Important Report Blah Blah" Lets figure out how to add a heading such as this to our document. Delete the bold lines of code below:

' Begin our PDF document
    objPDF.PDFBeginDoc
        ' Set the font name, size, and style
        objPDF.PDFSetFont FONT_ARIAL, 15, FONT_BOLD
        
        ' Set the text color
        objPDF.PDFSetTextColor = vbBlue
        
        ' Set the text we want to print
        objPDF.PDFTextOut "Hello, World! From mjwPDF (www.vb6.us)"
    
    ' End our PDF document (this will save it to the filename)
    objPDF.PDFEndDoc

And add these lines of code in their place:

' Lets add a heading
objPDF.PDFSetFont FONT_ARIAL, 15, FONT_BOLD
objPDF.PDFSetDrawColor = vbRed
objPDF.PDFSetTextColor = vbWhite
objPDF.PDFSetAlignement = ALIGN_CENTER
objPDF.PDFSetBorder = BORDER_ALL
objPDF.PDFSetFill = True
objPDF.PDFCell "A centered heading", 15, 15, _
    objPDF.PDFGetPageWidth - 30, 40

Let me explain what these mean. You should recognize the first line of code. It just sets the font info. Next we set the DrawColor (which in this case will be the highlight or inside color of our box). Next the text color is set and our alignment and border. The PDFSetFill=true tells mjwPDF to fill this box in when it prints out our text. The next line is what displays it all.
Let me break it down. The first parameter is simply the text we want displayed. Next we tell it how far over from the left we want the box (or cell) in our case we said 15 points over from the left. The next parameter is 15 points down from the top. Next we have to specify how wide the box is going to be. We want it to stretch all the way over to the right side of the page (minus the 15 point right border). To accomplish this we can use the mjwPDF classes PDFGetPageWidth function. This will give us the full width of the page we then subtract 30 off of it (15 for the left border and 15 for the right border), the last parameter is the height of the cell, 40 will be plenty high to accommodate our text.
If you run the code you should see your PDF pop up with a beautiful header at the top of the page.

Another fun thing is to create shapes in your PDF files. This can be used to create bar graphs or to highlight certain areas. Here is some sample code that creates a square.

' Lets draw a dashed red square
        objPDF.PDFSetLineColor = vbRed
        objPDF.PDFSetFill = True
        objPDF.PDFSetLineStyle = pPDF_DASHDOT
        objPDF.PDFSetLineWidth = 1
        objPDF.PDFSetDrawMode = DRAW_NORMAL
        objPDF.PDFDrawPolygon Array(300, 150, 400, 150, 400, 250, 300, 250)

Most the settings are self explanatory. Notice that you can specify the line style and the line width. Also notice that there is no draw square function. Instead there is a draw polygon function. It takes one parameter, but that parameter is an array of points specified in x y coordinates. X being how far from left to right to draw the point and Y being how far from top to bottom. So in our example we are specifying 4 points (the four corners of the square).

Point 1 is 300 pixels to the right, 150 pixels from the top
Point 2 is 400 pixels to the right, 150 pixels from the top
Point 3 is 400 pixels to the right, 250 pixels form the top
Point 4 is 300 pixels to the right, 250 pixels from the top.

Next lets draw an ellipse. An ellipse is simply a circle that can be squeezed either vertically or horizontally. To define it correctly we have to use some mathematical terms. If you remember from geometry class a circle has a radius. The radius is the distance from the center of the circle to the edge of the circle. An ellipse has two radiuses. One is horizontal the other is vertical. So the code for our ellipse is this:

' Lets draw an ellipse
        objPDF.PDFSetDrawColor = vbYellow
        objPDF.PDFSetLineColor = vbBlack
        objPDF.PDFSetLineStyle = pPDF_DASHDOT
        objPDF.PDFSetLineWidth = 1.25
        objPDF.PDFSetDrawMode = DRAW_DRAWBORDER
        objPDF.PDFDrawEllipse 300, 150, 75, 25

All the parameters should make sense by now. The new line is the PDFDrawEllipse call. Its a very simple call except that many times you think the x and y coordinates would correspond to the center of the circle. However, you would be wrong. Instead the first to parameters correspond to the upper left corner of the square that bounds the ellipse. The next two parameters specify the horizontal radius and the vertical radius respectively. If this seems confusing just run the program and you will see what I mean. The x & y parameters for our ellipse are the same as the x & y parameters for our first point in the square so you will see how it works. If you run the program you should see this:

Lets step back to text manipulation in PDF documents again. One thing you usually see in a professional document is the header like we did above. Another thing is usually page numbers in the footer. We can use the same logic we used for our header to add page numbers. I would like to add the numbers in the footer of the page on the right side, like most documents have. I'm not going to walk through how you can do this step by step, but here is the code for a visual basic subroutine that adds the page number to the bottom right corner of your PDF document.

' Adds the page number to the current page
Private Sub AddPageNumber(objPDF As mjwPDF, pageNumber As Integer)
    Dim sPageInfo As String
    Dim fontSize As Double
    Dim margin As Double

    fontSize = 10       'Size of font to use
    margin = 40         'Size of margin (left, right, bottom)
    
    ' Set what we want to print for page info
    sPageInfo = "Page " & pageNumber
    
    ' Should save these settings and change them back for more robust code
    objPDF.PDFSetTextColor = vbBlack
    objPDF.PDFSetAlignement = ALIGN_RIGHT
    objPDF.PDFSetFont FONT_ARIAL, Conversion.CInt(fontSize), FONT_NORMAL
    objPDF.PDFSetFill = False

    ' Uncomment the below line if you want to see how our formatting works
    'objPDF.PDFSetBorder = BORDER_ALL
    
    ' Draw the page number at the bottom of the page to the right
    objPDF.PDFCell sPageInfo, margin,  _
        objPDF.PDFGetPageHeight - margin - fontSize, _
        objPDF.PDFGetPageWidth - (margin * 2), fontSize

End Sub

Now that we know how to add page numbers how do we actually create multiple pages? Its very simple. When you are done with the first page, simply call the PDFEndPage method. Next call the PDFNewPage method to start the next page. Than just call the commands to add your text or shapes to the next page. You can do this as many times as you want. Don't forget to call the AddPageNumber method on each page though.
Another useful feature of PDF documents is adding bookmarks. Bookmarks allow you to jump from section to section in a PDF document easily. When the user views a PDF document with bookmarks, they are able to see a table of contents type tab on the left side of the screen. Note: if you want that pane to be visible by default you should add this line of code to the initializing section of your program.

' Lets us set see the bookmark pane when we view the PDF
    objPDF.PDFUseOutlines = True

Adding a bookmark is very easy in Visual Basic using mjwPDF. For instance lets add four bookmarks to our first page of our document. Call these anywhere in your code before you call the PDFEndPage method.

'Lets add a bookmark to the start of page 1
        objPDF.PDFSetBookmark "A. Page 1", 0, 0
        
        'Now a bookmark half way down page 1
        objPDF.PDFSetBookmark "A1. Page 1 Halfway down", 1, 300
        
        'Now one at the end page 1
        objPDF.PDFSetBookmark "A2. End of Page 1", 1, 500
        
        'Another one a little further down and shows nesting
        objPDF.PDFSetBookmark "A2-Sub1.", 2, 800

The first call to PDFSetBookmark creates a bookmark labeled "A. Page 1". The next parameter is the depth of this bookmark. Note: Start your depth at 0. The last parameter is the y position to where the bookmark will move the page. So the first call created a bookmark titled "A. Page 1" that points to the top of page 1. The next call creates a bookmark titled "A1. Page 1 halfway down". It has a depth of 1 (so it will be a child under our first bookmark) and it will scroll the page 300 points down. If you run the program you will see all the bookmarks created like this screen shows.

Another necessity to learn when creating PDF documents is how to add images to them. The mjwPDF class allows you to add any .jpg images to your PDF document. If the image is in a different format you will need to convert it to .jpg before you will be able to add it to your PDF file. However, if the image is a jpeg it is very easy to add it to the PDF doc. In the sample source code included with this tutorial you will see a logo.jpg file. Below is the code to end our first page and to start our second page. On the second page we add our logo to the upper left corner of the page.

objPDF.PDFEndPage
        
        'Start page 2
        objPDF.PDFNewPage
        
        'Lets add an image to page 2
        objPDF.PDFImage App.Path & "\logo.jpg", _
            15, 15, 50, 50, "http://www.vb6.us"

The highlighted code is what adds the logo. We call the PDFImage function. The first parameter is the path to the jpeg file. The next two parameters are the x and y coordinates for the logo. The next two parameters specify the width and height of the image. These parameters can be left off and then it will just display the picture in its original size. You can also specify just the height or width and it will scale the other side of the picture to keep it in proportion. The last parameter is also optional, but it allows you to specify a web site to go to if someone clicks on the image.
If you run your program now you will see a PDF file that has all the properties of a complete PDF document. Headers, shapes, images, and page numbers. Combining all these techniques you should be able to do just about anything you would want to. Download the Advanced PDF VB Tutorial source code to see the full sample.

The Java SE Development Kit 6 (JDK 6)

2011-12-23T20:22:00.001-08:00

You can download the Solaris OS or Linux version now. (Make sure you download the JDK, not the JRE.) Consult the installation instructions.
A text editor
In this example, we'll use Pico, an editor available for many UNIX-based platforms. You can easily adapt these instructions if you use a different text editor, such as vi or emacs.

These two items are all you'll need to write your first application.

Creating Your First Application

Your first application, HelloWorldApp, will simply display the greeting "Hello world!". To create this program, you will:

Create a source file
A source file contains code, written in the Java programming language, that you and other programmers can understand. You can use any text editor to create and edit source files.
Compile the source file into a .class file
The Java programming language compiler (javac) takes your source file and translates its text into instructions that the Java virtual machine can understand. The instructions contained within this .class file are known as bytecodes.
Run the program
The Java application launcher tool (java) uses the Java virtual machine to run your application.

Create a Source File

To create a source file, you have two options:

You can save the file HelloWorldApp.java on your computer and avoid a lot of typing. Then, you can go straight to Compile the Source File.
Or, you can use the following (longer) instructions.

First, open a shell, or "terminal," window.

A new terminal window.

When you first bring up the prompt, your current directory will usually be your home directory. You can change your current directory to your home directory at any time by typing cd at the prompt and then pressing Return.
The source files you create should be kept in a separate directory. You can create a directory by using the command mkdir. For example, to create the directory java in your home directory, use the following commands:

cd
mkdir java

To change your current directory to this new directory, you then enter:

cd java

Now you can start creating your source file.
Start the Pico editor by typing pico at the prompt and pressing Return. If the system responds with the message pico: command not found, then Pico is most likely unavailable. Consult your system administrator for more information, or use another editor.
When you start Pico, it'll display a new, blank buffer. This is the area in which you will type your code.
Type the following code into the new buffer:

/**
 * The HelloWorldApp class implements an application that
 * simply prints "Hello World!" to standard output.
 */
class HelloWorldApp {
    public static void main(String[] args) {
        System.out.println("Hello World!"); // Display the string.
    }
}

Be Careful When You Type

Note: Type all code, commands, and file names exactly as shown. Both the compiler (javac) and launcher (java) are case-sensitive, so you must capitalize consistently.

HelloWorldApp is not the same as helloworldapp.

Save the code in a file with the name HelloWorldApp.java. In the Pico editor, you do this by typing Ctrl-O and then, at the bottom where you see the prompt File Name to write:, entering the directory in which you wish to create the file, followed by HelloWorldApp.java. For example, if you wish to save HelloWorldApp.java in the directory /home/jdoe/java, then you type /home/jdoe/java/HelloWorldApp.java and press Return.
You can type Ctrl-X to exit Pico.

Compile the Source File into a `.class` File

Bring up another shell window. To compile your source file, change your current directory to the directory where your file is located. For example, if your source directory is /home/jdoe/java, type the following command at the prompt and press Return:

cd /home/jdoe/java

If you enter pwd at the prompt, you should see the current directory, which in this example has been changed to /home/jdoe/java.
If you enter ls at the prompt, you should see your file.

Results of the ls command, showing the .java source file.

Now are ready to compile the source file. At the prompt, type the following command and press Return.

javac HelloWorldApp.java

The compiler has generated a bytecode file, HelloWorldApp.class. At the prompt, type ls to see the new file that was generated: the following figure.

Results of the ls command, showing the generated .class file.

Now that you have a .class file, you can run your program.
If you encounter problems with the instructions in this step, consult the Common Problems (and Their Solutions).

Run the Program

In the same directory, enter at the prompt:

java HelloWorldApp

The next figure shows what you should now see.

The output prints "Hello World!" to the screen.

Congratulations! Your program works!

How to Install Drupal Tutorial

2011-12-23T20:17:00.000-08:00

Installing Drupal has gotten easier and easier throughout the years. It can be broken down into these steps.
1. Downloading and preparing the Drupal installation files
2. Setting up the mySQL database
3. Running the drupal install program

Downloading and preparing the Drupal install files

You can download the latest drupal installation files from drupal.org. Drupal compresses all of its install files in a tar and gzip format. To extract these on a windows machine you need a special program. There are many of them out there, but one of the easiest (and free open-source) ones to use is 7-Zip. Once you install 7Zip you can right click on the drupal.tar.gz file and click 7-Zip->Extract Here. Then you will have a drupal.tar file in the current folder. Right click and click 7-Zip->Extract Here again and you will have a drupal folder. Copy this folder to your web site folder. If you are using WAMP in the default location it will be c:\wamp\www.
I like to setup my sites under sub folders in the www folder. For example this site is located under the c:\wamp\www\drupal on my local computer. Another site I have that is for Visual Basic tutorials is located under c:\wamp\www\vb6. I highly recommend you do this as well. If you are only administering one site you probably can just copy all of the folders you extracted from the drupal install folder into your root web folder (c:\wamp\www) but than you can only have one drupal installation at a time.
If you do put the files in a sub directory under your main web folder here's what you can do to set things up so that it acts like its own web site (instead of a subdirectory of a web site). If you don't understand what I'm saying follow along and it will make sense. Say that you copied all of the drupal data into a sub folder of your www folder called mysite. Now your web site would be located at (c:\wamp\www\mysite). If you want to go to your web site through a web browser you would type in http://localhost/mysite. The problem is if you upload your new site to (www.mysite.com) anything images or files that are referenced through /mysite/ will be broke. Because of this we need to set up an alias so that you can type http://mysite.localhost into your web browser instead of http://localhost/mysite.
To do this we need to change a couple of files. The first is your apache configuration file (httpd.conf). If you are using the default WAMP install it will be located at c:\wamp\bin\apache\apache2.2.6\conf\http.conf. Open this file in a text editor and find the line that reads:

ServerName localhost:80

Now we are going to add our new sites virtual host information. Add these lines below the ServerName line.

NameVirtualHost *:80

<VirtualHost *:80>
  DocumentRoot "c:/wamp/www/"
  ServerName localhost
</VirtualHost>

<VirtualHost *:80>
  DocumentRoot "c:/wamp/www/mysite"
  ServerName mysite.localhost
  ServerAlias mysite
</VirtualHost>

This tells apache that anytime someone requests a server through http://localhost it should serve pages located at c:/wamp/www/ where as any time someone requests a server through http://mysite.localhost it should serve pages located at c:/wamp/www/mysite. Now if localhost was actually a domain we owned and registered than we would be done. However, localhost is a unique name that maps to our computer using the loop back ip (127.0.0.1) if you don't know what that means that's ok but basically you need to understand that anytime you type http://localhost Windows looks in a local file finds an entry for localhost and knows to route everything back to this computer. That file is is called the hosts file and it is located at C:\Windows\System32\drivers\etc\hosts. If you open this file in notepad you will see an entry that looks like this:

127.0.0.1       localhost

This tells Windows to reroute any requests to http://localhost back to our computer (127.0.0.1). Below this line we need to add a line that looks like this:

127.0.0.1       mysite.localhost

This will then tell windows to also route any requests to mysite.localhost to our computer. Than apache will see that we are requesting mysite.localhost instead of just localhost and will use the alias we set up in the httpd.conf to serve the files in c:\wamp\www\mysite. Few - that's a lot of explanation for just a few steps, but by understanding all this you are way ahead of the game if you ever want to host your own server down the road. Plus its just good to know.

Setting up the mySQL database

Now that our server settings are all set up and we have the drupal files copied over lets set up our mySQL database. In the past you had to install all of the SQL data using a script. Now, however, all you need to do is set up a blank database and know your username and password. We are going to use phpMyAdmin to do this. If you installed WAMP you can go to http://localhost/phpmyadmin. Once there type in the name for your database (i.e. mysite) under the Create new database field. Then click the Create button. That is it. You can also add a custom user that has access to your database by clicking the Privileges link. If you do this make sure you give that user full access to everything in your mysite database.

Now that you have the database set up you need to remember (or write down) the following information. The name of your database, the username, and the password to access the database.

Running the drupal install program

Installing Drupal is one of the easiest parts of this Drupal tutorial. All you have to do is go to http://mysite.localhost and you should see a screen that looks like this:

If I were you I would probably select english (unless of course you understand another language or want to have some fun). After that it should skip by the verifying requirements sections unless you have an error in your setup. If this is the case follow the onscreen instructions to solve your error.
Next you will enter the database information I told you to remember earlier. Enter the database info, the username, and the password. I would leave the advanced settings alone for now. Click the Save and Continue button.
You should now see a status screen as drupal is installed. It usually only takes a minute or two. Next the Drupal install program will ask you to set up your new site! Type in the info it asks. Make sure you remember the administrator username and password (this is different than your database username and password). You will use this to log in to your drupal site and administer all the different settings and to add content. Also if you have the option I suggest you turn on clean URL's. If you don't do this your web page addresses will look like this http://mysite.localhost/index.php?q='mypage'. If you do turn on clean URL's the same page will have the address http://mysite.localhost/mypage. Also this makes your Drupal site much more likely to be indexed and ranked well by search engines. This is called making your site Search Engine Friendly.
Once you are done setting you will have a full fledged drupal web site up and running. Check it out at mysite.localhost. Have a look around, start changing the default settings. Read on this site more tutorials and have a great time. Once you get your site online somewhere post a comment below letting me know or send me an email. I love to see what people are doing with these tutorials.

Computer Tutorials, A Learning Experience For Everyone

2011-12-23T20:13:00.001-08:00

Welcome!
Thanks for taking a moment visiting this site! Here we have guaranteed computer tutorials completely free for you with simple step by step procedures to help you learn and visualize your work with confidence.
My goal is to give you a solid foundation. Whether you have specific questions about computers (example, Why is my computer running slow?, how do I create a folder in Windows?), or simply feel like learning everything, here you will find the information that you're looking for and gain the skills you need to keep your computer running at its best.
Let me ask you something, do you feel frustrated because your computer's performance is very slow and is not working the way you want? Do you feel that your computer gets slower every time you use it and you don’t have the money to take it to the computer technician to fix it?
Perhaps you wish there was a magic button that you could press and fix everything.
Though I cannot tell you that a button will fix your computer, I can however promise you that by investing a little bit of your time, I can show you, through a series of computer tutorials how to fix those frustrating every day computer problems.
You will discover through this site that most computer problems can be fixed quite easily by keeping simple maintenance on your PC, and by understanding how they work.
So jump in and begin checking these computer tutorials out. You can start first looking into the free computer help and the basic computer troubleshooting pages below. This site is tailored to everyone who has always wanted to learn about computers, but has not had the chance to do so. In other words, this site is for you. I will show you ways to keep your computer up and running just the way you want it.
From now on, the next time you find yourself with a computer problem, just relax, take a deep breath, and check out this web site. You will find the solution here.

How to Change Post Title Background Color in Blogger Templates

2011-12-23T20:10:00.000-08:00

A reader emailed me and asked about changing the post title background color, so here it is. In this tutorial I'll tell you how to change the post title background color in Blogspot templates. As usual, we'll do some editing in the template's HTML code and after that, you'll be able to change the post background color from Blogger Template Designer. So, here are the simple steps to follow:

How to Add Background Color to Sidebar Widget Titles

Posted by Bilal | Wednesday, October 05, 2011 | 0 Comments

A reader has requested this tutorial to add background color to sidebar titles. By default, Blogger only allows to change the font & color of sidebar widget titles. So, I'll try to make it as simple as possible & you'll be able to change the background color from the Template Designer.

Final Image Preview

2011-12-23T20:07:00.000-08:00

Step 1

Create a new document with 1000×1000 pixels. Press D to set up foreground and background color as black (#000000) and white (#ffffff). Fill layer with black color (#000000).

Create a new layer, name it “Planet” and apply Filter > Render > Clouds.

Step 2

Apply Filter > Artistic > Plastic Warp (Highlight Strength:20, Detail:15, Smoothness: 2).

Apply Filter > Distort > Polar Coordinates.

Apply Filter > Distort > Polar Coordinates again but changing option to Rectangular to Polar.

Use Filter > Distort > Sphesize.

Apply Filter > Distort > Sphesize one more time updating Amount to 50%.

Apply Filter > Distort > Pinch.

Step 3

With Elliptical Marquee Tool select planet. After that invert selection with Select > Inverse.

Press Delete to clear selected area and remove selection with Ctrl+D.

Go to Edit > Transform > Scale to reduce planet size a bit (keep pressing SHIFT to maintain proportions).

Step 4

Duplicate “Planet” layer with Ctrl+J and apply Filter > Brush Strokes > Dark Strokes (Balance: 5, Black Intensity: 2, White Intensity: 2).

Update layer mode to Soft Light on current layer.

Step 5

Duplicate again “Planet” layer with Ctrl+J and move it on top of all layers. Apply Filter > Sketch > Bas Relief (Detail: 13, Smoothness: 2, Light: Bottom Right).

Update layer mode to Soft Light on current layer.

Select Eraser Tool (Opacity: 30%, Size: 40px) and with a soft round brush remove the relief of ice surfaces.

Step 6

Duplicate again “Planet” layer with Ctrl+J and move it on top of all layers. Apply Filter > Sketch > Note Paper (Image Balance: 25, Graininess: 10, Relief: 11).

Update layer mode to Overlay on current layer.

Select Eraser Tool again (Opacity: 30%, Size: 40px) and with a soft round brush remove too monotonous plots.

Step 7

Duplicate again “Planet” layer with Ctrl+J and move it on top of all layers. Apply Filter > Sketch > Plaster (Image Balance: 20, Smoothness: 2).

Update layer mode to Darker Color on current layer.

Select Eraser Tool again (Opacity: 30%, Size: 100px) and with a soft round brush clean almost all layer content leaving only some areas to soften edges.

Step 8

Duplicate again “Planet” layer with Ctrl+J and move it on top of all layers. Update layer mode to Hard Light on current layer.

Step 9

Create a new layer on top of all other layers. Update layer mode to Screen, select Brush Tool (Opacity: 10%, Size: 90px) and lighten up darker water areas with white color.

Step 10

Create a new layer on top of all other layers. Update layer mode to Color. Set foreground color to #47639a, select Brush Tool (Opacity: 10%, Size: 400px) and start applying to ice areas of the planet. If necessary increase or decrease a little brush opacity.

Step 11

We need to add stars around the planet. With Brush Tool selected and a soft round brush (Size: 5px to start) press F5 to access brush presets.

Set foreground color to white, create a new layer on top and draw the stars. Keep updating brush size.

Step 12

Create a new layer on top. Press D button to set up foreground and background colors as black and white and apply Filter > Render > Clouds.

Update layer mode to Linear Dodge.

Select Eraser Tool (Opacity: 30%) and clean layer so that the result should be similar to mine.

Step 13

To finish tutorial, create a new layer on top and update layer mode to Soft Color. Select Brush Tool with soft round brush (Opacity: 15-20%), choose color #263c35 and start decorating nebula. Keep updating brush color like i did and you will have a great result.

Final Image

Poster from the Words

2011-12-23T20:05:00.000-08:00

Note: Original tutorial published on Photoshopstar.com had too many errors. Being one the most popular tutorials, tutorial was rewritten and now published.

Resources

Girl

Step 1

First we are going to create a new document in Photoshop with 1000×1000 pixels with all the default settings. Fill the background layer with black color.

Step 2

Select Horizontal Type Tool and place any text (white color, caps on) filling all background as you can see below. Only use one text layer.

Rotate text 45 degrees CW by going to Edit > Transform > Rotate.

Step 3

Open photo and copy it to our canvas over text layer. Go to Edit > Free Transform and resize photo to something similar to what I have.

Select text from text layer (Ctrl+left click on text layer thumbnail in layers). Press Ctrl+Shift+I to invert selection. With selection active, select photo layer and press Delete to clear selection contents.

Press Ctrl+D to deactivate selection.

Apply Image > Adjustments > Brightness/Contrast.

Step 4

Create a new layer. Select Horizontal Tool (Arial, white color, 55px) and write something like “DJ Music!”. Create a second new layer and write “Only best hits!” (Arial, white color, 25px).

Rotate text 45 degrees CW by going to Edit > Transform > Rotate on both layers.

Apply Layer > Layer Style > Blending Options > Outer Glow to both layer to make it more readable.

Final Image

This tutorial is now finished and you got a great effect!

III. Backlinks – Another Important SEO Item

2011-12-23T20:01:00.001-08:00

What are Backlinks?

In layman's terms, there are two types of links: inbound and outbound. Outbound links start from your site and lead to an external site, while inbound links or backlinks, come from an external site to yours. e.g. if cnn.com links to yourdomain.com, the link from cnn.com is a backlink (inbound) for yourdomain.com, however the link is an outbound link from cnn.com's perspective. Backlinks are among the main building blocks to good Search Engine Optimisation (SEO).

Why Backlinks Are Important

The number of backlinks is an indication of the popularity or importance of that website. Backlinks are important for SEO because some search engines like Google, give more credit to websites that have a large number of quality backlinks, and consider those websites more relevant than others in their results pages for a search query.

Therefore, when search engines calculate the relevance of a site to a keyword, they not only consider the number of backlinks to that site but also their quality. In order to determine the quality, a search engine considers the content of the sites. When backlinks to your site come from other sites, and those sites have content related to your site, these backlinks are considered more relevant to your site. If backlinks are found on sites with unrelated content, they are considered less relevant. The higher the relevance of backlinks, the greater their quality.

For example, if a webmaster has a website about how to rescue orphaned dogs, and received a backlink from another website about dogs, then that would be more relevant in a search engine's assessment than say a link from a site about car racing. Therefore, higher the relevance of the site linking back to your website, the better the quality of the backlink.

Search engines want websites to have a level playing field, and look for natural links built slowly over time. While it is fairly easy to modify your webpages to make them more SEO friendly it is a lot harder for you to influence other websites and get them to link to your website. This is the reason search engines regard backlinks as a very important factor. Further, search engine's criteria for quality backlinks has gotten even tougher, thanks to unscrupulous webmasters trying to achieve these backlinks by deceptive or sneaky techniques, such as hidden links, or automatically generated pages whose sole purpose is to provide backlinks to websites. These pages are called link farms, and they are not only disregarded by search engines, but linking to a link farm could get your site banned entirely.

Keywords – the Most Important Item in SEO

2011-12-23T19:59:00.000-08:00

Keywords are the most important SEO element for every search engine, they are what search strings are matched against. Choosing the right keywords to optimize for is thus the first and most crucial step to a successful SEO campaign. If you fail on this very first step, the road ahead is very bumpy and most likely you will only waste your time and money. There are many ways to determine which keywords to optimize for and usually the final list of them is made after a careful analysis of what the online population is searching for, which keywords have your competitors chosen and above all - which are the keywords that you feel describe your site best.

1. Choosing the Right Keywords to Optimize For

It seems that the time when you could easily top the results for a one-word search string is centuries ago. Now, when the Web is so densely populated with sites, it is next to impossible to achieve constant top ratings for a one-word search string. Achieving constant top ratings for two-word or three-word search strings is a more realistic goal.
For instance, If you have a site about dogs, do NOT try and optimize for the keyword "dog" or "dogs". Instead you could try and focus on keywords like "dog obedience training", "small dog breeds", "homemade dog food", "dog food recipes" etc. Success for very popular one-two word keywords is very difficult and often not worth the trouble, it's best to focus on less competitive highly specific keywords.
The first thing you need to do is come up with keywords that describe the content of your website. Ideally, you know your users well and can correctly guess what search strings they are likely to use to search for you. You can also try the Website Keyword Suggestions Tool below to come up with an initial list of keywords. Run your inital list of keywords by the Google keyword Suggestion tool, you'll get a related list of keywords, shortlist a couple of keywords that seem relevent and have a decent global search volume.

Introduction – What Is SEO

2011-12-23T19:58:00.001-08:00

Whenever you enter a query in a search engine and hit 'enter' you get a list of web results that contain that query term. Users normally tend to visit websites that are at the top of this list as they perceive those to be more relevant to the query. If you have ever wondered why some of these websites rank better than the others then you must know that it is because of a powerful web marketing technique called Search Engine Optimization (SEO).
SEO is a technique which helps search engines find and rank your site higher than the millions of other sites in response to a search query. SEO thus helps you get traffic from search engines.
This SEO tutorial covers all the necessary information you need to know about Search Engine Optimization - what is it, how does it work and differences in the ranking criteria of major search engines.

1. How Search Engines Work

The first basic truth you need to know to learn SEO is that search engines are not humans. While this might be obvious for everybody, the differences between how humans and search engines view web pages aren't. Unlike humans, search engines are text-driven. Although technology advances rapidly, search engines are far from intelligent creatures that can feel the beauty of a cool design or enjoy the sounds and movement in movies. Instead, search engines crawl the Web, looking at particular site items (mainly text) to get an idea what a site is about. This brief explanation is not the most precise because as we will see next, search engines perform several activities in order to deliver search results – crawling, indexing, processing, calculating relevancy, and retrieving.
First, search engines crawl the Web to see what is there. This task is performed by a piece of software, called a crawler or a spider (or Googlebot, as is the case with Google). Spiders follow links from one page to another and index everything they find on their way. Having in mind the number of pages on the Web (over 20 billion), it is impossible for a spider to visit a site daily just to see if a new page has appeared or if an existing page has been modified, sometimes crawlers may not end up visiting your site for a month or two.
What you can do is to check what a crawler sees from your site. As already mentioned, crawlers are not humans and they do not see images, Flash movies, JavaScript, frames, password-protected pages and directories, so if you have tons of these on your site, you'd better run the Spider Simulator below to see if these goodies are viewable by the spider. If they are not viewable, they will not be spidered, not indexed, not processed, etc. - in a word they will be non-existent for search engines.

tutorial application

Database Access with the Data Control

Visual Basic & ADO Tutorial

Creating PDF files in Visual Basic

The Java SE Development Kit 6 (JDK 6)

Creating Your First Application

Create a Source File

Compile the Source File into a .class File

Run the Program

How to Install Drupal Tutorial

Downloading and preparing the Drupal install files

Setting up the mySQL database

Running the drupal install program

Computer Tutorials, A Learning Experience For Everyone

How to Change Post Title Background Color in Blogger Templates

How to Add Background Color to Sidebar Widget Titles

Final Image Preview

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Step 8

Step 9

Step 10

Step 11

Step 12

Step 13

Final Image

Poster from the Words

Resources

Step 1

Step 2

Step 3

Step 4

Final Image

III. Backlinks – Another Important SEO Item

What are Backlinks?

Why Backlinks Are Important

Keywords – the Most Important Item in SEO

1. Choosing the Right Keywords to Optimize For

Introduction – What Is SEO

1. How Search Engines Work

Compile the Source File into a `.class` File