Connection Object

This object represents an open connection to the data source. This connection can be a local connection (say App.Path) or can be across a network in a client server application. Some of the methods and properties of this object are not available depending on the type of data source connected to.
Key Properties




Name

Data Type

Description


ConnectionString

String

Defines in string form the location of the data source you wish to connect to. Key fields in the string you will use are the "Provider=" and the "Data Source=" fields. You may also use the "Mode=" field. See descriptions of those properties for a more detailed view of each one. Some examples of connection strings follow:

Data Source=c:\test.mdb;Mode=Read|Write;Persist - Connects to an Access database with read/write/persist permissions

driver={SQL Server) server=bigsmile;uid=sa;pwd=pwd;database=pubs - Connects to an SQL Server database called bigsmile as user sa with password pwd to database pubs.


Provider

String

A string defining the provider of a connection object. An example follows:

Provider=Microsoft.Jet.OLEDB.4.0 -This string connects to a MS Jet 4.0 compliant database (an Access DB for example.)


Mode

connectModeEnum

Sets (or returns) the permissions for modifying data across the open connection. Available permissions include Read, Write, Read/Write, and various Share/Deny types.


CursorLocation

cursorLocationEnum

Sets the location of the cursor engine. You can select client side or server side, for simpler databases (like Access) client side is the only choice.


ConnectionTimeout

Long

Time in seconds that a connection will attempt to be opened before an error is generated.


CommandTimeout

Long

Time in seconds that an command will attempt to be executed before an error is generated.

Key Methods



Name

Description


Close

Closes an open connection.


Open

Opens a connection with the settings in the ConnectionString property.

Command Object

A command object specifies a specific method you intend to execute on or against the data source accessed by an open connection.
Key Properties


Name

Data Type

Description


ActiveConnection

conConnection as ADODB.Connection

Defines the Connection object the command belongs to.


CommandText

String

Contains the text of the command you want to execute against a data source. This can be a table name, a valid SQL string, or the name of a stored procedure in the data source. Which one you use is determined by the CommandType property.


CommandType

commandTypeEnum

Defines the type of the command. The three most commonly used would be adCmdText, adCmdTable, and adCmdStoredProc. The setting adCmdText causes the CommandText string to be evaluated as an SQL string. The setting adCmdTable causes the CommandText string to be evaluated as a table name and will return the all of the records and columns in a table. The setting adCmdStoredProc causes the CommandText string to be evaluated as a stored procedure in the data source.

Key Methods


Name

Description


Execute

Executes the query, SQL statement, or stored procedure specified in the CommandText property and returns a RecordSet object.

RecordSet Object

The RecordSet object represents a complete set of records from an executed command or from an underlying base table in the database. A key thing to note is that a RecordSet object references only one record at a time as the current record.
Key Properties


Name

Data Type

Description


CursorLocation

CursorLocationEnum

This sets or returns the location of the cursor engine for the database. The options are client side and server side, for less advanced databases like Access you may only be able to select client side.


CursorType

CursorTypeEnum

Sets the cursor type. CursorType typically determines when and to whom database changes are immediately visible to. For client side cursor locations only one type of CursorType is available, adOpenStatic.


EOF and BOF

Boolean

End Of File and Beginning Of File. When at the first record of an open RecordSet BOF will be true, when at the last EOF will be true. If you ever return a RecordSet with no records then EOF and BOF will be true. This provides an ideal way of testing if any records have been returned by a query.


Fields

Collection

Returns a collection of the field objects for an open record set. Database fields can be accessed by their name using the RecordSet!FieldName schema, by their index RecordSet.Fields(intIndex) or sometimes by their name from the fields collection RecordSet.Fields("FieldName"). I find in the situation where you know a database structure that the RecordSet!FieldName method is best, where structure is not known, then the RecordSet.Fields(intIndex) may be best.


LockType

LockTypeEnum

sets the lock type on records when they are open for editing. If a client side cursor location is selected then only optimistic and batch optimistic locking are available.


RecordCount

Long

Returns the number of records in an open RecordSet. If for some reason ADO cannot determine the number of records then this will be set to -1.

Key Methods


Name

Description


AddNew

Sets up an open record set to add a new record, once the required values have been set call the Update (or UpdateBatch) method to commit the changes.


Close

Closes an open RecordSet object, make sure that any changes are committed using the Update (or UpdateBatch) method before closing or an error will be generated.


MoveNext

Causes an open RecordSet object to move to the next record in the collection, if the current record is the last record then EOF is set to true.


MoveFirst

Causes an open RecordSet object to move to the first record in the collection.


MoveLast

Causes an open RecordSet object to move to the last record in the collection.


Open

Opens the RecordSet, typically this is done with a valid Command object that includes the command text (or SQL) to get the records you want.


Update

Causes any changes made to the current record to be written to disk.


UpdateBatch

Causes any changes you have made in batch mode to be written to disk. The way to use batch updates is to open the RecordSet object with the LockType adLockBatchOptimistic.

Putting It All Together

I like to think of using ADO as a three step process


Define and open a Connection to a data source
Decide what data you need from the data source and define Command objects that will retrieve this data.
Retrieve the data you require by executing the command objects and manipulate the data using RecordSet objects.

To start using ADO create a new Standard EXE project in your VB6 environment. You'll need to add a reference to the ADO library using the Project -> References menu and selecting the "Microsoft ActiveX Data Objects 2.5 Library" or the "Microsoft ActiveX Data Objects 2.6 Library". Then on the form add a single command button and add the code below to the click event of the button.

Save the project and then open Microsoft Access and create a new database called "database.mdb" in the directory where you have saved your VB project. Add a table called "tabTestTable" to the database. Add two columns to this table with a text data type, the column names do not matter. Add a couple of records to the table with some values in the columns.

Then you can safely run your project and see the results.

If you dont want to do this I have created a VB project that includes all of this that can be downloaded. Just unzip this to a directory and open the project in Visual Studio and try it out.

Got questions? Feel free to ask me at questions@timesheetsmts.com Why not
to help cover the bandwidth this page chews up and you'll be much more likely to get an answer! Private Sub Command1_Click()

'Define the three objects that we need,
' A Connection Object - connects to our data source
' A Command Object - defines what data to get from the data source
' A RecordSet Object - stores the data we get from our data source

Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset


'Defines the connection string for the Connection. Here we have used fields
'Provider, Data Source and Mode to assign values to the properties
' conConnection.Provider and conConnection.Mode

conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\" & "database.mdb;Mode=Read|Write"


'Define the location of the cursor engine, in this case we are opening an Access database
'and adUseClient is our only choice.

conConnection.CursorLocation = adUseClient


'Opens our connection using the password "Admin" to access the database. If there was no password
'protection on the database this field could be left out.

conConnection.Open


'Defines our command object

' .ActiveConnection tells the command to use our newly created command object.
' .CommandText tells the command how to get the data, in this case the command
' will evaluate the text as an SQL string and we will return all
' records from a table called tabTestTable
' .CommandType tells the command to evaluate the .CommandText property as an SQL string.

With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tabTestTable;"
.CommandType = adCmdText
End With

'Defines our RecordSet object.

' .CursorType sets a static cursor, the only choice for a client side cursor
' .CursorLocation sets a client side cursor, the only choice for an Access database
' .LockType sets an optimistic lock type
' .Open executes the cmdCommand object against the data source and stores the
' returned records in our RecordSet object.

With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With

'Firstly test to see if any records have been returned, if some have been returned then
'the .EOF property of the RecordSet will be false, if none have been returned then the
'property will be true.

If rstRecordSet.EOF = False Then

'Move to the first record

rstRecordSet.MoveFirst

'Lets move through the records one at a time until we reach the last record
'and print out the values of each field

Do

'Access the field values using the fields collection and print them to a message box.
'In this case I do not know what you might call the columns in your database so this
'is the safest way to do it. If I did know the names of the columns in your table
'and they were called "Column1" and "Column2" I could reference their values using:

' rstRecordSet!Column1
' rstRecordSet!Column2


MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)

'Move to the next record

rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True

'Add a new record

With rstRecordSet
.AddNew
.Fields(0) = "New"
.Fields(1) = "Record"
.Update
End With

'Move back to the first record and delete it

rstRecordSet.MoveFirst
rstRecordSet.Delete
rstRecordSet.Update


'Close the recordset

rstRecordSet.Close
Else
MsgBox "No records were returned using the query " & cmdCommand.CommandText
End If

'Close the connection

conConnection.Close

'Release your variable references

Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
End Sub

More SQL Keywords

In the previous chapter, we have learned to use the basic SQL keywords SELECT and FROM to manipulate database in Visual Basic 6 environment. In this lesson, you will learn to use more SQL keywords. One of the more important SQL keywords is WHERE. This keyword allow the user to search for data that fulfill certain criteria.

The Syntax is as follows:

SELECT fieldname1,fieldname2,.....,fieldnameN FROM TableName WHERE Criteria

The criteria can be specified using operators such as =, >,<, <=, >=, <> and Like.

Using the database books.mdb created in the previous chapter, we will show you a few examples. First of all, start a new project and insert a DataGrid control and an ADO control into the form. . At the ADODC property pages dialog box, click on the Recordsource tab and select 1-adCmdText under command type and under Command Text(SQL) key in SELECT * FROM book. Next, insert one textbox and put it on top of the DataGrid control, this will be the place where the user can enter SQL query text. Insert one command button and change the caption to Query. The design interface is shown below:

Run the program and key in the following SQL query statement

SELECT Title, Author FROM book WHERE Author='Liew Voon Kiong'

Where you click on the query button, the DataGrid will display the author name Liew Voon Kiong. as shown below:

Query based on year

Run the program and key in the following SQL query statement:

SELECT * FROM book WHERE Year>2005

When you click on the query button, the DataGrid will display all the books that were published after the year 2005.

You can also try following queries:

SELECT * FROM book WHERE Price<=80

SELECT * FROM book WHERE Year=2008

SELECT * FROM book WHERE Author<>'Liew Voon Kiong'

You may also search for data that contain certain characters by pattern matching. It involves using the Like operator and the % symbol. For example, if you want to search for a author name that begins with alphabet J, you can use the following query statement

SELECT * FROM book WHERE Author Like 'J%'

Where you click on the query command button, the records where authors' name start with the alphabet J will be displayed, as shown below:

Next, if you wish to rank order the data, either in ascending or descending order, you can use the ORDER By , ASC (for ascending) and DESC(Descending) SQL keywords.

The general formats are

SELECT fieldname1, fieldname2.....FROM table ORDER BY fieldname ASC

SELECT fieldname1, fieldname2.....FROM table ORDER BY fieldname DESCExample 28.3:

The following query statement will rank the records according to Author in ascending order.

SELECT Title, Author FROM book ORDER BY Author ASC

　　

The following query statement will rank the records according to price in descending order.

SELECT Title, Price FROM book ORDER BY Price DESC

Accessing databases using the ADO Data Control

The Database Design

1. Open up MS Access (Start - Programs - Microsoft Access)
2. Pick "Start a blank database" from the wizard that pops up
3. Pick a spot to save the mdb file and a name for it (mine was "CDCollectionA.mdb")
4. You'll get to the following window, where you double click on "Create a table in design view:"

the database design main window

5. When you double click that "create table in design view" thingie, you get to this window:

The table design view window

6. You want to follow the following steps to get the table I was working with:
   1. Make a field called ArtistName whose type is Text
   2. Make a field called AlbumTitle whose type is Text
   3. Make a field called Tracks whose type is Number (just a long integer is cool enough)
   4. Select the rows in the design view (as pictured above) that have ArtistName and AlbumTitle
   5. Right-click on that selection, and pick Primary Key from the menu you get. This will make both fields into primary keys. The idea is that they can be primary because you'll never have identical artist names and album titles (otherwise what's the point?!).
7. Once you've got your table built, just close that window. You'll be automatically prompted to save changes to the table design and to give the table a name. I picked CDs, how original. :)
8. Once that's all done, you can either add a couple entries to the database by double clicking the CDs table from the database design main window and inputting them manually or just move on to:

Software Design
Okay, as mentioned at the top of this article, we're going to use the ADO (ActiveX Data Objects) data control for getting at data instead of the common data control used in Example 3. I know what you're thinking: who cares. :) In any case, ADO can provide you with a little more customizability and a little bit more speed, but it's also a little more difficult to use. No problem, though, there's a cheap way around everything, so let's dive right in.

1. First thing first, start up VB with a Standard Exe project.
2. Go to the Project menu and select Components (near the bottom). You want to add two new controls to your project:
   • the Microsoft ADO Data Control (OLEDB) and
   • the Microsoft Hierarchical FlexGrid Control 6.0 (note this is not the FlexGrid as used in Example 3)

the add components dialog

3. Okay, now do the following stuff to your main form:
   1. Change the caption of your main form to something hip and jive ...
   2. Add a Heirarchical FlexGrid to your form by picking the tool and drawing it on your main form.
   3. Add an ADO data source control to the form using the tool and drawing on the form. Change its visibility property to False.
   4. Add two frames to the form using the tool and drawing them on the form.
      • Change for one frame:
        • its caption to Add Entry
        • its (name) to fraAddEntry
      • Change for the other (second) frame:
        • its caption to Remove Entry
        • its (name) to fraRemoveEntry
   5. Draw the following controls in the Add Entry frame (yes, actually in the frame):
      1. A text box with the (name) txtArtistName
      2. A label above that text box with the caption Artist Name
      3. A text box with the (name) txtAlbumTitle
      4. A label above that text box with the caption Album Title
      5. A text box with the (name) txtTrackCount
      6. A label above that text box with the caption Number of Tracks
      7. A command button with the (name) cmdAddEntry and the caption Add this info
   6. Now, to the Remove Entry frame, add the following controls:
      1. A command button with the (name) cmdRemoveEntry and the caption Remove Selected
      2. A label with the caption Select the entry you want to remove and click the button:
4. Now, the most complicated part is formatting the Heirarchical FlexGrid (which is called MSFHlexGrid1) to do what you want. It's fairly customizable, but here's all I did for this example program:
   1. the AllowBigSelection property was set to False
   2. the AllowUserResizing property was set to 0
   3. the FixedCols property was set to 0 while the FixedRows property was set to 1 (this is recommended for pretty displaying of stuff)
   4. the FocusRect property was set to 0
   5. the HighLight property was set to 1
   6. the ScrollBars property was set to 2
   7. the ScrollTrack property was set to True
   8. the SelectionMode property was set to 1 (selection by row only)
5. In the form design window, double click the form, which should bring up the code window with a blank Form_Load() subroutine.
   • You'll notice this time that we do the data and database hookup in code instead of in the property sheet.
   • For the ConnectionString property of the ADO Data Control, you can build it in the property sheet. There's a wizard in there that will do it from scratch for you, but it's pretty annoying to surf through. That's why I just included this string instead (it was built in the wizard though)
   • You need two global variables, and they appear right above the Form_Load() method.
   • For the FlexGrid DataSource, note that the Set command/directive/whatever is used!! Here's the code:

Option Explicit

' couple'o global vars to track the form minimum size
Dim MinHeight As Long
Dim MinWidth As Long

Private Sub Form_Load()    
    ' set up the database connectivity for the ADO data control
    With Adodc1
        
        ' the connection string just defines an interface to connect
        ' to the Access database.  We use the MS Jet SQL drivers for
        ' simplicity's sake.  Side-note: you can build your own connection string
        ' from the property sheet for the ado data control, but I would advise
        ' against it, this is easier. :)
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            App.Path & "\CDCollection.mdb;Persist Security Info=False"
            
        ' the record source just tells the data control what and how
        ' to pull out of the database. Just raw SQL here.
        .RecordSource = "select * from CDs order by ArtistName"
    End With
    
    ' set the Flex Grid data source to be the ADO data control.
    Set MSHFlexGrid1.DataSource = Adodc1
    
    ' set up the format string for the flex grid.
    MSHFlexGrid1.FormatString = "Artist Name | Album Title | Track Count"
    
    ' position all the controls happily and store the form minimum size
    MinHeight = Form1.Height
    MinWidth = Form1.Width
    Call Form_Resize    
End Sub

6. Okay, that's done. From the event ComboBox at the top of the code window, pick the Resize event. You should then get a shell for the Form_Resize() method. This gets called whenever you resize the form, and we'll just use it to make a resized form look pretty. Here's what to fill in:

Private Sub Form_Resize()    
    ' check to see if the form is getting too small (Note: this is just to avoid
    ' the math necessary to shrink all the textboxes, hahahaha!!)
    If MinHeight > Form1.Height Then
        Form1.Height = MinHeight
        Exit Sub
    ElseIf MinWidth > Form1.Width Then
        Form1.Width = MinWidth
        Exit Sub
    End If

    ' resize the flexgrid to fit nicely on the screen
    MSHFlexGrid1.Width = Form1.ScaleWidth
    MSHFlexGrid1.Height = Form1.ScaleHeight / 2

    ' resize the happy columns to look pretty (40% for each text column, 20% for Track)
    MSHFlexGrid1.ColWidth(0) = 0.4 * MSHFlexGrid1.Width
    MSHFlexGrid1.ColWidth(1) = MSHFlexGrid1.ColWidth(0)
    MSHFlexGrid1.ColWidth(2) = MSHFlexGrid1.Width - (MSHFlexGrid1.ColWidth(0) * 2) - 60
    
    ' reposition  and resize the frames on the screen to fit nicely (there was no
    ' science here, just did it by trial and error)
    fraAddEntry.Top = (Form1.ScaleHeight / 2) + 100
    fraAddEntry.Height = (Form1.ScaleHeight / 2) - 150
    fraAddEntry.Width = (Form1.ScaleWidth * 0.64)
    fraRemoveEntry.Height = (Form1.ScaleHeight / 2) - 150
    fraRemoveEntry.Top = (Form1.ScaleHeight / 2) + 100
    fraRemoveEntry.Width = (Form1.ScaleWidth * 0.36) - 100
    fraRemoveEntry.Left = fraAddEntry.Width + 100
End Sub

7. Now, go back to the form design window and double click the Add this info button. You should now have a blank cmdAddEntry_Click() subroutine. The code is pretty much identical to the old database example, but here's what to fill in, anyways:

Private Sub cmdAddEntry_Click()
    ' add a new entry to our table.
    With Adodc1.Recordset
        .AddNew
        !ArtistName = txtArtistName
        !AlbumTitle = txtAlbumTitle
        !Tracks = txtTrackCount
        .Update
        .Requery
    End With
        
    ' refresh the data source and rebind it to the flexgrid (annoying!!)
    Adodc1.Refresh
    Set MSHFlexGrid1.DataSource = Adodc1
    MSHFlexGrid1.FormatString = "Artist Name | Album Title | Track Count"
    Call Form_Resize

    ' clear the text fields once the new record is added
    txtArtistName = ""
    txtAlbumTitle = ""
    txtTrackCount = ""
    
    ' set the focus back to the  artist name textbox
    txtArtistName.SetFocus
End Sub

8. Now you need the remove code. In the form design window, double-click the Remove Selected button. You should get a shell for the cmdRemoveEntry_Click() subroutine. This is the code:

Private Sub cmdRemoveEntry_Click()
    ' delete an entry from the database
    With Adodc1.Recordset
        .Move (MSHFlexGrid1.Row - 1) ' we minus one because row zero is the header row
        .Delete
        .Requery
    End With
    
    ' refresh the data source and rebind it to the flexgrid (annoying!!)
    Adodc1.Refresh
    Set MSHFlexGrid1.DataSource = Adodc1
    MSHFlexGrid1.FormatString = "Artist Name | Album Title | Track Count"
    Call Form_Resize
    
    ' set the focus back to the first add field
    txtArtistName.SetFocus
End Sub

9. Okay, if you go to the form design window, you have three textboxes: txtArtistName, txtAlbumTitle, and txtTrackCount. Double click on each of them in turn to get their associated Change methods and fill in the following code:

Private Sub txtArtistName_Change()
    ' here, just check to see if each text field has contents.  If they all have
    ' contents (ie, they're not empty) enable the "Add Entry" button.
    If txtArtistName.Text <> "" And txtAlbumTitle.Text <> "" And txtTrackCount.Text <> "" Then
        cmdAddEntry.Enabled = True
    Else
        cmdAddEntry.Enabled = False
    End If
End Sub

Private Sub txtAlbumTitle_Change()
    ' just call the artist name change method because the code here would be
    ' exactly the same.
    Call txtArtistName_Change
End Sub

Private Sub txtTrackCount_Change()
    ' just call the artist name change method because the code here would be
    ' exactly the same.
    Call txtArtistName_Change
End Sub

10. While you're still in the txtTrackCount_Change() method, go to the event ComboBox at the top of the code window and select the KeyPress event. You should get a shell for the txtTrackCount_KeyPress(KeyAscii as Integer) method. Here's the rest of the code for that, it just filters out alphabetic and punctuation characters:

Private Sub txtTrackCount_KeyPress(KeyAscii As Integer)
    ' TrackKey will store which key was pressed in an _ascii_ value.
    Dim TrackKey As String
    TrackKey = Chr(KeyAscii)
    
    ' if the key pressed was a)not a number and b) not the backspace key,
    ' just erase the keystroke (it won't get processed or sent)
    If (Not IsNumeric(TrackKey) And Not (KeyAscii = vbKeyBack)) Then
        KeyAscii = 0
    End If
End Sub

The line and Shape controls

Graphics is a very important part of visual basic programming because an attractive interface will be appealing to the users. In the old BASIC, drawing and designing graphics are considered difficult jobs, as they have to be programmed line by line in a text-based environment. However, in Visual Basic 6, these jobs have been made easy. There are four basic controls in VB6 that you can use to draw graphics on your form: the line control, the shape control, the image box and the picture box

To draw a straight line, just click on the line control and then use your mouse to draw the line on the form. After drawing the line, you can then change its color, width and style using the BorderColor, BorderWidth and BorderStyle properties.Similarly, to draw a shape, just click on the shape control and draw the shape on the form. The default shape is a rectangle, with the default shape property set at 0. You can change the shape to square, oval, circle and rounded rectangle by changing the shape property’s value to 1, 2, 3 , 4, and 5 respectively. In addition, you can change its background color using the BackColor property, its border style using the BorderStyle property, its border color using the BorderColor pproperty as well its border width using the BorderWidth property.

The program in this example allows the user to change the shape by selecting a particular shape from a list of options from a list box, as well as changing its color through a common dialog box. 

The objects to be inserted in the form are a list box, a command button, a shape control and a common dialog box. The common dialog box can be inserted by clicking on ‘project’ on the menu and then select the Microsoft Common Dialog Control 6.0 by clicking the check box. After that, the Microsoft Common Dialog Control 6.0 will appear in the toolbox; and you can drag it into the form. The list of items can be added to the list box through the AddItem method. The procedure for the common dialog box to present the standard colors is as follows:

CommonDialog1.Flags = &H1&

CommonDialog1.ShowColor

Shape1.BackColor = CommonDialog1.Color

The last line will change the background color of the shape by clicking on a particular color on the common dialog box as shown in the Figure 18.1 below:

The Interface. 

Figure 18.1

The color dialog box

The Code

Private Sub Form_Load()

List1.AddItem "Rectangle"

List1.AddItem "Square"

List1.AddItem "Oval"

List1.AddItem "Circle"

List1.AddItem "Rounded Rectangle"

List1.AddItem "Rounded Square"

End Sub

Private Sub List1_Click()

Select Case List1.ListIndex

Case 0

Shape1.Shape = 0

Case 1

Shape1.Shape = 1

Case 2

Shape1.Shape = 2

Case 3

Shape1.Shape = 3

Case 4

Shape1.Shape = 4

Case 5

Shape1.Shape = 5

End Select

End Sub

Private Sub Command1_Click()

CommonDialog1.Flags = &H1&

CommonDialog1.ShowColor

Shape1.BackColor = CommonDialog1.Color

End Sub 

Creating and Reading files using Common Dialog Box

This example uses the common dialog box to create and read the text file, which is much easier than the previous examples.Many operations are handled by the common dialog box. The following is the program:

Dim linetext As String Private Sub open_Click() CommonDialog1.Filter = "Text files{*.txt)|*.txt" CommonDialog1.ShowOpen If CommonDialog1.FileName <> "" Then Open CommonDialog1.FileName For Input As #1 Do Input #1, linetext Text1.Text = Text1.Text & linetext Loop Until EOF(1) End If Close #1 End Sub Private Sub save_Click() CommonDialog1.Filter = "Text files{*.txt)|*.txt" CommonDialog1.ShowSave If CommonDialog1.FileName <> "" Then Open CommonDialog1.FileName For Output As #1 Print #1, Text1.Text Close #1 End If End Sub The syntax CommonDialog1.Filter = "Text files{*.txt)|*.txt"ensures that only the textfile is read or saved .The statement CommonDialog1.ShowOpen is to display the open file dialog box and the statement CommonDialog1.ShowSave is to display the save file dialog box. Text1.Text = Text1.Text & linetext is to read the data and display them in the Text1 textbox The Output window is shown below:

Crystal Reports Tutorial

Crystal Reports is an application used to design and create reports. It can gather and process data from a wide range of sources and can turn it into useful information. The application is a product of SAP AG, and is mostly popular in small organizations. Not only can the application gather data from a variety of sources, it can also deploy reports through .Net, HTML, and Visual Basic. A Crystal Reports tutorial generally teaches small business owners and their employees how to develop reports so that they can be useful in the decision making process.

In this blog post, we will discuss the features of this application, why you should learn it, what can be accomplished through it, and what parts should a good Crystal Reports Tutorial cover

Crystal Reports Features

Crystal reports has built in features that allow users to design reports easily. The features include pre-built templates, ready-to-use data drivers, and flexible charting capabilities. The application is compatible with popular business software suites such as Microsoft Office. The dashboard and reports can be embedded in presentations to add value to them. The tools also empower the user to create visualizations with the help of charts and graphs. You can also build interactive dashboard using Crystal Reports.

The best feature of Crystal Reports and the main reason of its popularity is that it can extract data from all major types of data sources. From spreadsheets to large databases, the application can consolidate data from multiple sources into reports and dashboards. You can browse through data, sort it, filter it, explore the generated reports, and share the reports through the internet or through a secure server.

Finally, one of the most important features of Crystal Reports is that it can generate ‘what if’ scenarios. After going through a Crystal Reports tutorial online, an advanced user can easily create model reports to visualize what would happen if certain business decisions are made. In short, it is the ideal software application to have at your workplace if you do not have enough resources to buy large scale business software suites.

Why Learn Crystal Reports

Consider this scenario. You are the owner of a small organization and have a limited budget. You do have the basic IT infrastructure at your workplace, i.e. computers and an internet connection, but you do not have enough resources to buy and implement an ERP. With every transaction you make and every operation that takes place in your organization, useful data is generated and just sits there in spreadsheets. In this scenario, wouldn’t it be great if there was a cost effective way to utilize that data, and to use it to make business decisions?

We bet most of you agree that the data being gathered at an organization is a knowledge resource that cannot be neglected. Such data can be used to find out your strengths and weaknesses, to indicate inefficiencies and flaws in operations, to identify patterns, and to make calculated predictions.

However, you cannot sort, filter, and process such data and turn it into useful reports with the help of a spreadsheet. If the data is in large quantities, you need a way to turn it into useful information efficiently and effectively. That is where Crystal Reports can really help you out.

What You Can Accomplish Through Crystal Reports

Anyone with a working knowledge of computer can learn how to use Crystal Reports, and can start gathering data and turning it into useful information. The point and click features on Crystal Reports, the easy to understand interface, and the visual clarity of the generated reports all make this application a must have for your office.

Any report or visualization created through Crystal Reports can easily be incorporated in an MS Office presentation, and you can email the reports to your business partners, suppliers, distributors, etc. With the help of visualizations and predictive models, you can see what will happen as a result of business decisions you are about to make. While you should not entirely depend on Crystal Reports for your decision making process, having predictive models and past reports at hand will help you make educated and informed decisions.

What You Should Learn in Crystal Reports Tutorial

When taking an online Crystal Reports course, you should focus on learning how to design reports from the first step. You should learn to extract data from spreadsheets and how to connect to Oracle databases, Access databases, and SQL servers. You should learn how to sort, filter, and group data, and you should learn how to customize reports with business logic.

A thorough Crystal Reports tutorial will teach you how to write a basic report, how to format it, how to connect to remote data sources, and how to highlight important information. With the help of Crystal Reports, you can merge data from multiple tables and create business queries; you can also embed sub-reports into reports, and can add all types of visuals. After you learn how to accomplish such tasks, you can move towards learning more complex tasks such as deploying Crystal Reports to different formats, publishing reports online, and integrating Crystal Reports with custom applications.

For a small business owner, Crystal Reports can make the decision making process more effective and efficient. You can make informed decisions and can gain a competitive advantage over others if you use this application correctly. For someone in the market for a job, learning Crystal Reports adds an important skill to your resume, a skill that other candidates may not have. Crystal Reports is a useful business application, and is a must have for anyone who has a reasonable collection of useful data.

We hope this blog post helped you gain a basic understanding of Crystal Reports and why you should try a Crystal Reports Tutorial. Feel free to contact us if you have any questions or comments, we will love to hear what you have to say.

ADO Database access in VB6

To modify or view a database from VB6 is relatively easy, but there is no good method built into Visual Basic (there is a method, but I won't discuss it in this article). The best way is to use the COM based ADO objects.
Step 1: Reference ADO
The first thing to do is to add a reference to the ADO objects needed. This can be done by going to the menu and selecting Project -> References. At this point you will be presented with a screen, scroll though the list until you get to 'Microsoft ActiveX Data Access Objects x.x Library' On my computer I have several different versions of the library. I always select the highest version, which is v2.8 in my case. Even if you have a lower version, don't worry about it, what I'll be showing you will still work.


Step 2: Create a Form
I've created a simple form below that only contains 1 label and 1 textbox, the text box is named txtColor.


Step 3: Connection object
Now that we have referenced the ADO objects and a form for the project we can get started on the code. First thing to decide is which kind of database are you connecting to? In my example I will be connecting to a Microsoft Access Database, but if you wan to connect to another one, you can choose one of these below connection strings.
MS Access ODBC =  Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\DatabasePath\dbaccess.mdb;Uid=;Pwd=;
MS Access Jet (I will use this one) =  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabasePath\MmDatabase.mdb;User Id=admin;Password=;
Oracle ODBC =  Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;
DB2 = Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=127.0.0.1;Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;Default Schema=MySchema;User ID=MyUsername;Password=MyPassword;
SQL Server = Provider=sqloledb;Network Library=DBMSSOCN;Data Source=127.0.0.1,1433;Initial Catalog=MyDatabaseName;User ID=MyUserName;Password=MyPassword;
Step 4: Database
I created a sample database called 'db1.mdb'. It only contains 1 table called 'Table1'. This table has 2 fields, 'ID' (Number, Long Integer) & 'Color' (Text 50 characters). Obviously this table is only a sample so doesn't have much in it, but I added some dummy data so that we can perform a SELECT on the db.

Step 4: Code
Option Explicit

Private Sub Form_Load()

Dim C As New ADODB.Connection
C.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;User Id=admin;Password=;"

Dim Rec As New ADODB.Recordset
Dim SQL As String
SQL = "SELECT ID, Color FROM Table1 WHERE ID = 2"
Rec.Open SQL, C, adOpenForwardOnly, adLockReadOnly
Do Until Rec.EOF

Me.txtColor.Text = Rec.Fields("Color").Value
Rec.MoveNext

Loop
Rec.Close
Set Rec = Nothing 'Insert a new record
C.Execute("INSERT INTO Table1 (ID, Color) VALUES (4, 'Black')")
C.Close

 End Sub

Step 5: Run the app
Just press F5, and the application starts. And you will see the form below.

Create Simple Calculator in VB.6

Visual Basic 6.0 is a programming language developed by Microsoft which can be easily learned and used by beginner, as well as advanced programmers. Though it is no longer supported by Microsoft, thousands of applications still run on it and many more are still being developed. This guide will show you how to create a simple calculator in Visual Basic 6.0

Open Visual Basic 6.0 and create a new Standard EXE Project. Standard EXE projects give you a handful of commands and tools, useful to develop simple as well as semi-complex programs.
You can also choose a VB Enterprise Edition Project which will give you a lot more tools to work with. For a beginner programmer, it is suggested to use a Standard EXE Project.

Understand the project screen. In the center of the screen will be a box with a lot of dots. This is your form. A form is the place where you will add the various elements (command buttons, pictures, text boxes, etc) to your program. To the left of the screen is the toolbox. The Toolbox contains various pre-defined elements of any program. You can drag and drop these elements onto your form

• 
• Understand the project screen. In the center of the screen will be a box with a lot of dots. This is your form. A form is the place where you will add the various elements (command buttons, pictures, text boxes, etc) to your program.

• • To the left of the screen is the toolbox. The Toolbox contains various pre-defined elements of any program. You can drag and drop these elements onto your form.
    
  • To the lower right of the screen is the form layout. This determines where your program will be displayed on the screen once the project is complete and executed.
    
  • On the mid-right is the properties box which determine the property of any element that is selected in a form. You can change various properties using this. If no element is selected, it displays the properties of the form.
    
  • On the top-right is the project explorer. It shows the various designs, forms that are included in a project.
    
  • If any of these boxes is missing, you can add them by clicking on the "View" button on the Menu bar.
    
• 
  3
  Drag a label onto the form, and change the caption of the label to "Enter first number".
  • The caption of a label can be changed using the properties box.
    
• 
  4
  Create a textbox to the right of the first label. Remove any text that appears inside the textbox by changing blanking the "Text" field in properties box.
• 
  5
  Create another label and change the caption to "Enter second number" and create another textbox to its right.
• Drag and create four command buttons below these two labels. Change the caption of these command buttons to "Add", "Subtract", "Multiply", "Divide" respectively.
• 
  7
  Create another label with a caption "Result" and a textbox to the right of it below the four command buttons. This textbox will be used to display the result. With this, your design is complete.
• 
  8
  To start coding, in the project explorer, click on the form and then select the left-most button. It will take you to the coding screen.
  • Click on the list box in the top-left of the coding screen. One by one, click on all the commands (Command1, Command2, etc) so that the outline coding of them will be visible to you on your coding screen.
    
• 9
  Declare the variables. To declare:
  • Dim a, b, r as Integer
    
  • a is the value entered in the first textbox, b is the value entered in the second textbox and r is the result. You can any other variables too.
• 10
  Start the coding for the add command (Command1). The code will be as follows:
  • Private Sub Command1_Click()
    a = Val(Text1.Text)
    b = Val(Text2.Text)
    r = a + b
    Text3.Text = r
    End Sub
    
• 11
  Code for the subtract command (Command2). The code will be as follows:
  • Private Sub Command2_Click()
    a = Val(Text1.Text)
    b = Val(Text2.Text)
    r = a - b
    Text3.Text = r
    End Sub
    
• 12
  Code for the multiply command (Command3). The code will be as follows:
  • Private Sub Command3_Click()
    a = Val(Text1.Text)
    b = Val(Text2.Text)
    r = a * b
    Text3.Text = r
    End Sub
    
• 13
  Code for the divide command (Command4). The coding will be as follows:
  • Private Sub Command4_Click()
    a = Val(Text1.Text)
    b = Val(Text2.Text)
    r = a / b
    Text3.Text = r
    End Sub
    
• 
  14
  Click the start button or press F5 to execute your program.
  • Test all the commands and see if your program is working.
    
• 
  15
  Save your project and your form. Make your project and save it as a .exe file on your computer; run it whenever you want!