Database - ADO Beginners Tutorial, Some Further Steps.
In the thread ADO Tutorial for Classic VB (or Beacon's original version), you learnt to build an application which (using ADO code) showed data from a database on your form, and allowed you to add/delete data.
If you haven’t already read the tutorial, now would be a good time, as it covers many of the concepts that we will use here.
There are many different things that you may want to do, and hopefully a good deal of your questions will be answered here... but it is unlikely that all your questions will be, as the world of database applications is huge, and very varied.
Save the current record
One of the things missing from Beacon’s tutorial (and has been asked for a few times) is a way to save changes to the current record. This is actually very similar to part of the code that is shown (cmdAdd_click), but with one line removed – the AddNew.
You do not need to specify that a record is being edited, as this is assumed as soon as you set any of the field values.
Now that you know this, perhaps you will want to have the Add button just adding a ‘blank’ record on screen. To do this your Add button will use just clear the textboxes/combo, and set a Boolean variable (which is declared in the "General Declarations" section of the form), and check that at the start of the Save button – if the Boolean has been set, then run the AddNew line. The code could look like this:
VB Code:
'in declarations
Private booIsAdding as Boolean
Private Sub cmdSave_Click()
With rs
If booIsAdding Then .AddNew
.Fields("field2") = text1.text 'setting field2 = whatever is typed in text1
.Fields("field3") = text2.text 'as above
.Fields("field1") = combo1.text 'as above
.Update 'this updates the recordset etc.
End With
booIsAdding = False 'revert back to "edit" mode
End Sub
Private Sub cmdAdd_Click()
booIsAdding = True 'enter "add" mode
text1.text = ""
text2.text = ""
combo1.ListIndex = -1
End Sub
Show only specific data from the database
In the tutorial you simply loaded all of the data from a table in the database, but what do you need to do if you only want your program to show some of the data?
The answer to this starts out nice and simple (use an SQL statement), but unfortunately can get complicated quite quickly (depending on what you want to do).
The only part of your code that needs to change is the rs.Open line, which first of all needs to change from this:
VB Code:
rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable
To this:
VB Code:
rs.Open "SELECT * FROM tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdText
…or preferably (to make the code easier to read), this:
VB Code:
Dim strSQL as String
strSQL = "SELECT * FROM tbl_master"
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
Now you can specify conditions for the data to be shown. For the sake of simplicity, I will only show the strSQL version.
Lets suppose that you want to only see the records where Field2 (which is a numeric field) is equal to 7. To do this, you would use this SQL:
VB Code:
strSQL = "SELECT * FROM tbl_master WHERE Field2 = 7"
Nice and simple! :)
(if you want, you can use > 7 , or >=7 , etc)
How about where Field3 (a text field) is equal to: hello
VB Code:
strSQL = "SELECT * FROM tbl_master WHERE Field3 = 'hello' "
(any text values need to be inside single quotes)
You can make this more complex if you like, such as:
VB Code:
strSQL = "SELECT * FROM tbl_master WHERE Field3 = 'hello' And (Field2 = 7 OR Field2 = 9) "
How about a search button?
As seen in the previous section, you can easily load data based on criteria. The simplest way to add search functionality to your program is to re-load the recordset using SQL that you build from the search controls. This could be code like this:
VB Code:
Private Sub cmdSearch_Click()
Dim strSQL as String
'build the SQL statement based on what the user typed in [I]txtSearch[/I]
strSQL = "SELECT * FROM tbl_master"
If txtSearch.Text <> "" Then
strSQL = strSQL & " WHERE Field2 = " & Val(txtSearch.Text)
End If
'close the recordset (required before reloading it)
rs.close
'load the new data
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
'show the data
Fillfields
End Sub
If you want to search based on multiple fields, see the end of the section above to see what the finished SQL should look like (using And/Or as appropriate). Note tho that as the first item needs to have "Where" before it, but the others need And/Or, you need to build strSQL a little differently, eg:
VB Code:
Dim strSQL as String, strWhere as String
'build the SQL statement based on what the user typed in [I]txtSearch[/I] and [i]txtAnotherSearch[/i]
strSQL = "SELECT * FROM tbl_master"
'(find the conditions)
If txtSearch.Text <> "" Then strWhere = strWhere & " AND Field2 = " & Val(txtSearch.Text)
If txtAnotherSearch.Text <> "" Then strWhere = strWhere & " AND Field7 = " & Val(txtAnotherSearch.Text)
'(put the conditions into the SQL statement, without the first And)
If strWhere <> "" Then
strSQL = strSQL & " WHERE " & Mid(strWhere, 5)
End If
Note that this method does not allow you to look at records that don’t match your search, unless you do another search without specifying any criteria. If you only want to "move" to a matching record (so you can then move next to see non-mathcing data), try using rs.Find or rs.Seek (VB’s help is your best guide for these ;) ).
There are many more things that you may want to do, however this is just to answer some of the common questions that arise based on the tutorial, and to give you some ideas of what can be done.
If you have any further questions, you may well be able to find the answer in the Database Development FAQ, if not then please post a question in the Database Development forum.