Bookmark and Share




Visual Basic 2010 / SQL - Database III (2020)
visual basic 2010




Database III

Database Tables

Now we are ready to display on the Form.

  1. Make sure Form Designer is open and then click Data, Show Data Sources to open the Data Sources window.

  2. Select the Cars icon and then choose the Details option from the dropdown arrow.

    carsDetailsOption
  3. Expand the Cars tree and select any item. Then, click the arrow button that appears to see a list of possible controls. Let's choose ComboBox for ModelID and VersionID, and for the rest, choose Textbox.

    comboBoxModelID
    comboBoxVersionID
    priceTextBox
    noteTextBox
  4. Let's click on the Cars icon in the Data Sources window and then drag it across the IDE and drop it onto the Form in Form Designer. Then, we'll see lots of controls get automatically added to the Form. We'll also see some items get added to the Component tray.

    automaticallyGeneratedItems

    • TableAdapterManager is the top-level component that coordinates the operations of TableAdapters.
    • TableAdapter is the data access object that has Fill and GetData() method to actually supply data to the controls.
    • DataSet contains data tables of the in-memory representation of the database tables.
    • BindingSource is an intermediate manager between the dataset and Form controls.
  5. Build and run the application.

    runResult





Binding Data

Since we've added data controls to the Form, we can not display the data contained in the Cars table but the ModelID and VersionID fields are still displaying the ID number which is not the associated value from the linked table. It is necessary to bind the linked tables to those controls so the application displays meaningful data.

  1. Click on the Models table icon in the Data Sources window. Then, drag it to Form Designer and drop it onto the ModelID ComboBox control. Check to see ModelsBindingSource and ModelsTableAdapter items get added to the Component Tray.

    modelsBindingSource

  2. Click on the Versions table icon in the Data Sources window. Then, drag it to Form Designer and drop it onto the VersionID ComboBox control. Check to see VersionsBindingSource and VersionsTableAdapter items get added to the Component Tray.

    versionsBindingSource

  3. Click the arrow button on each ComboBox control to reveal their new data binding settings on the Smart Tag.

    modelsComboBoxTask
    modelsComboBoxTaskAfter

    The ModelID ComboBox is now bound to ModelsBindingSource so will now display the Model value, rather than its ID number.

  4. Similarly, the VersionID ComboBox is now bound to the VersionsBindingSource. So, it will display the Version value rather than its ID number.

    versionsComboBoxTaskAfter

  5. Edit the Label control alongside each ComboBox to remove the ID text so that it can reflect the new value these controls will display.

  6. Because the CarsID is not really meaningful to the user, let's set its Visible property to False in the Properties window and then delete its Label control.

    changingLabels
  7. To test the ability to save data permanently back to the database and enter some text in the Note field and click the Save Data button. Then, restart the application and check if the new text has been preserved.

    testingSave





SQL Queries

Now that we added the ability to display meaningful data, we can exploit the power of databases by building custom SQL queries to extract only specific data.

  1. Select the CarsTableAdapter icon in the Component Tray. Then, choose Add Query from its Smart Tag options to launch the Search Criteria Builder dialog.

    addQuery

    searchCriteriaBuilder

    The Search Criteria Builder dialog displays an SQL query named FillBy that is executed by the Form's Load event-handler to populate the navigationToolStrip and Form fields. This query selects all columns and rows of the Cars table. It can be recreated as a custom SQL query that can be executed to perform the same service whenever the user requires all data to be selected.


  2. Change the New Query Name field to GetAll, then click OK. Then, we'll see another ToolStrip get added to the Form containing a button labeled GetAll.

    getAllSearchCriteriaBuilder

    getAllButton
  3. Select the new ToolStrip. Then, in the Properties window, set its AutoSize to False and change its MaximumSize and Size properties to resemble a single button 100, 30.

  4. To create custom SQL queries that select specific data we can simply edit the default query by appending a qualification clause.

    Click the CarsTableAdapter component icon and choose Add Query to open Search Criteria Builder again.

    whereVersionID3

    Change the New Query Name field to GetClassics and then append the WHERE VersionID = 3 to the Query Text statement.


  5. Click OK to create a ToolStrip for this query and resize it as before to resemble a single button.

  6. Reopen Search Criteria Builder and Create a new Query named GetSUV appending this to the Query Text WHERE ModelID = 5.

  7. Resize the new ToolStrip as before. Then, run the application and click the GetClassics button to select data on all Classic versions only.

    getClassicRun

  8. Click the GetAll button to select all data once more. Then, click GetSUV to select data on all SUV models only.

    getSUVsRun

  9. Here is the CarsDatabaseDataSet diagram which is also written into the file .

    carsDatabaseDataSet