Results 1 to 1 of 1

Threaded View

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Bristol, UK

    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:
    1. 'in declarations
    2. Private booIsAdding as Boolean
    4. Private Sub cmdSave_Click()
    5.   With rs
    6.     If booIsAdding Then .AddNew
    7.     .Fields("field2") = text1.text 'setting field2 = whatever is typed in text1
    8.     .Fields("field3") = text2.text 'as above
    9.     .Fields("field1") = combo1.text 'as above
    10.     .Update 'this updates the recordset etc.
    11.   End With
    12.   booIsAdding = False 'revert back to "edit" mode
    13. End Sub 
    15. Private Sub cmdAdd_Click()
    16.   booIsAdding = True 'enter "add" mode
    17.   text1.text = ""
    18.   text2.text = ""
    19.   combo1.ListIndex = -1
    20. 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:
    1. rs.Open "tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    To this:
    VB Code:
    1. rs.Open "SELECT * FROM tbl_master", cn, adOpenKeyset, adLockPessimistic, adCmdText
    …or preferably (to make the code easier to read), this:
    VB Code:
    1. Dim strSQL as String
    2.   strSQL = "SELECT * FROM tbl_master"
    3.   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:
    1. 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:
    1. 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:
    1. 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:
    1. Private Sub cmdSearch_Click()
    3. Dim strSQL as String
    4.     'build the SQL statement based on what the user typed in [I]txtSearch[/I]
    5.   strSQL = "SELECT * FROM tbl_master"
    6.   If txtSearch.Text <> "" Then
    7.     strSQL = strSQL & " WHERE Field2 = " & Val(txtSearch.Text)
    8.   End If
    10.      'close the recordset (required before reloading it)
    11.   rs.close
    12.      'load the new data
    13.   rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
    14.      'show the data
    15.   Fillfields
    16. 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:
    1. Dim strSQL as String, strWhere as String
    2.     'build the SQL statement based on what the user typed in [I]txtSearch[/I] and [i]txtAnotherSearch[/i]
    3.   strSQL = "SELECT * FROM tbl_master"
    4.                    '(find the conditions)
    5.   If txtSearch.Text <> "" Then strWhere = strWhere & " AND Field2 = " & Val(txtSearch.Text)
    6.   If txtAnotherSearch.Text <> "" Then strWhere = strWhere & " AND Field7 = " & Val(txtAnotherSearch.Text)
    7.                    '(put the conditions into the SQL statement, without the first And)
    8.   If strWhere <> "" Then    
    9.     strSQL = strSQL & " WHERE " & Mid(strWhere, 5)
    10.   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.
    Last edited by si_the_geek; Jan 6th, 2009 at 05:38 PM. Reason: corrected typo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.