|
|
|
|
Note: This article was previously published on VB/MySQL.com.
Introduction
I answer a large number of questions from people having problems with their Visual Basic programs. Once I have the details of their situation I find that many people are using Data Binding techniques. While Data Binding is a powerful technique, it can also lead to many problems that would not otherwise exist. My efforts to explain this have ranged from difficult to impossible. The purpose of this article is to detail what Data Binding is, why it should be avoided and how to write VB programs without it. Later portions of this article refer to a sample program which can be download from http://www.vbmysql.com/download/no_data_binding.zip.
What is Data Binding?
Data Binding is a programming technique introduced in Visual Basic 3.0 that allows one to “Write a program without coding”. Microsoft created a component called a Data Control and added Data Bound capabilities to it's standard components. This allowed a programmer to use the properties of these components to establish a relationship between a data source and user interface components via the property browser. Based on this relationship the components handle the reading and writing of the data automatically. This frees the programmer from the complexity of interacting with the database. Data Binding is so simple to use that someone who has never programmed before can write a functional program in minutes.
Why shouldn't I use Data Binding?
If Data Binding is so great then why is it not a good idea to use it? The answer to this question comes down to program complexity. If you are making a small, single user program that will utilize an Access database then Data Binding may be an appropriate technique. The problem comes in when you need to do something more complex. Data Binding works by embedding the reading and writing logic into the data control and other ActiveX controls. This logic was written by Microsoft as long time ago and was optimized to work with their products only. While it is possible to use Data Binding with non-Microsoft databases, support for this is not complete and will likely never be. When you write the database reading and writing logic yourself you can make adjustments as necessary. You gain the needed flexibility to utilize many different data sources and the ability to react properly to unusual data access needs.
How can I tell if I'm using Data Binding?
Many of the people that I have helped never realized that they were using Data Binding. They simply followed the instructions that they were given and didn't understand the pitfalls. You are using Data Binding if you use the data control (or the ADODC) and have set the DataSource property of another control to reference the data control. You are also using Data Binding if you are using the Data Environment. Popular data bound controls are the DataGrid and the DataCombo but most ActiveX controls can be bound.
The control that I'm using will only work when bound.
Many controls utilize Data Binding as their
primary mode. Most of these controls have an Unbound or Additem mode. If not
then there are always other controls that provide the same (or similar)
functionality. The DataGrid is a popular example of a data binding centric
control. Most DataGrid examples assume that you are using Data Binding. In our
example program I will show how to use a ListView control in report mode to
provide equivalent functionality.
If I'm not using a data control then what do I use to access my data?
The data control (or ADODC) is a visual component that you draw on the screen. It provides you with a means to point at a database and a table. It also provides buttons to move between records. We will utilize a connection object and a recordset object to provide similar capabilities. The connection and recordset objects are code components. You create them with code instead of drawing them. To create these objects we must first include a reference to the Microsoft Active Data Objects. You can do this by using the References screen under the Project menu. Scroll down until you see Microsoft Active Data Objects and check the box beside it.
The following code block gives an example of how to create, open and close a connection and a recordset.
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& "DATABASE=test;" _
& "UID=testuser;" _
& "PWD=12345;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.Open
rs.Open "SELECT * FROM mytable WHERE 1=0", conn, adOpenStatic, adLockOptimistic
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Lets look a bit closer at this code. The first four lines:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
create our objects. This step is equivalent to drawing the data control. The next several lines:
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& "DATABASE=test;" _
& "UID=testuser;" _
& "PWD=12345;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.Open
rs.Open "SELECT * FROM mytable WHERE 1=0", conn, adOpenStatic, adLockOptimistic
tell the objects where to get their data from. The last four lines:
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
close and destroy our objects when we are
finished with them. You cannot use them after they have been closed and
destroyed.
Changing the current record
The data control has several direction buttons that allow the user to change the current record. If you only have a few records then this record scrolling technique works fairly well. If you have a lot of records then the user needs a more sophisticated means of finding the record that they want. Recordsets don't have any visual aspect so we will have to provide another means to move between records. We will do this with a Key field. Every table (regardless of design) has to have at least one unique field in order to be updatable. We can have our user enter a value for this field, then retrieve the correct record based on this value. If the user enters a key value that doesn't exist we can treat this as an instruction to start a new record. As an assist we will be populating a preview of the data in a listview. Double clicking this listview can signal that the user wants to retrieve that record.
Updating a record
When using Data Binding, a field gets updated when you tab out of a bound control. A new record gets added when you scroll past the last record using the buttons on the data control. When using recordsets you will have to perform these tasks with code. The following code starts a new record, assigns a value to a field and updates the record.
rs.AddNew
rs.Fields("Name") = "Johnny Bravo"
rs.Fields("address") = "Mama's House"
rs.Update
Putting it all together
The rest of this article refers to the sample program that goes with the article. You can download a copy of the program from http://www.vbmysql.com/download/no_data_binding.zip. You will probably want to look through it as you continue reading. If you get an error when trying to run the sample program then you are using a different version of ADO then I am. You will need to add a reference to your version of ADO using the references screen on the Projects menu.
Below is a screen shot of a generic connection screen that I use. On this screen I'm collecting the information needed to connect to a MySQL server. In larger applications you will want to store this information in a configuration file instead of asking the user each time.
There is quite a bit of code behind this screen but the part we want to look at is in the cmdConnect_Click of the frmConnect form.
Set gCnn = New ADODB.Connection
gCnn.ConnectionTimeout = 60
gCnn.CommandTimeout = 400
gCnn.CursorLocation = adUseClient
gCnn.Open "DRIVER={MySQL ODBC 3.51 Driver};" _
& "user=" & txtUserName _
& ";password=" & txtPassword _
& ";database=" & cmbDatabase _
& ";server=" & txtHost _
& ";option=" & (1 + 2 + 8 + 32 + 2048 + 16384)
I have previously dimmed gCnn as a global connection object. The code above opens this connection based on the values entered. Another important block of code can be found in the CreateTables procedure in the same form.
Sub CreateTables()
Dim pRs As ADODB.RecordsetSQL = "CREATE TABLE IF NOT EXISTS `mytable` (" _
& "`Name` varchar(100) NOT NULL default '', " _
& "`Address` varchar(100) NOT NULL default '', " _
& "`CTZ` varchar(100) NOT NULL default '', " _
& "`Notes` text NOT NULL, " _
& "PRIMARY KEY (`Name`) " _
& ") TYPE=MyISAM"
gCnn.Execute SQL, , adExecuteNoRecords
Set pRs = New ADODB.Recordset
SQL = "Select Name, Address, CTZ, Notes From mytable "
pRs.Open SQL, gCnn, adOpenStatic, adLockOptimistic, adCmdText
If pRs.EOF Then
'No records yet so we'll add some
With pRs
.AddNew
.Fields("Name") = "Johnny Bravo"
.Fields("Address") = "Mama's House"
.Fields("CTZ") = "Springfield, MO 12345"
.Fields("Notes") = "You watch this show?"
.Update
' *** Extra code removed ***
End With
End If
pRs.Close
Set pRs = NothingEnd Sub
This code shows how to do two important things: execute queries with a connection object and how to use a recordset to insert a record. I'm entering some sample data so that we'll have something to play with.
The following screen shot shows how an unbound data entry screen might look.
The user either types in a name and tabs out of the name field or double clicks a row in the listview. This triggers the retrieval of the record or the start of a new record. We update the form caption to let them know which. Each of the buttons at the bottom have code in their click events to carry out their functions. Nothing on this data entry screen is bound and everything used either comes with VB6 or can be downloaded for free from Microsoft. For the sake of brevity I won't go over all the code. If you have any questions about what a specific method does, you can look in MSDN for the full details.
This sample screen is very simplistic. If my table had a lot of records I would only load a few (maybe 100) records at a time and give the user a means of paging through the data. I would also add a search button that would allow more random movement through the data set. I'll leave these topics for future articles.
Summary
Is Data Binding bad? Not really. Is it appropriate for serious VB programming?
No. Writing the code yourself requires that you learn quite a bit more about
programming and will likely take longer but it is worth the effort. By taking
the time to use code you gain considerable flexibility and when you run into a
problem it will be in code that you can see and fix. While it may seem like a
lot of work you can speed things up considerably by using templates and generic,
reusable routines. With practice you can make screens like this in under 30
minutes and have the comfortable feeling that you know exactly what is going on.
