# Visual Basic > Visual Basic FAQs >  Database - ADO Beginners Tutorial, Some Further Steps.

## si_the_geek

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 declarationsPrivate 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" modeEnd Sub  Private Sub cmdAdd_Click()  booIsAdding = True 'enter "add" mode  text1.text = ""  text2.text = ""  combo1.ListIndex = -1End 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!  :Smilie: 
(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  FillfieldsEnd 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  :Wink:  ).



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.

----------

