- VB.Net Basic Tutorial
Syntax is the set of rules by which the elements of a language are correctly combined. SQL syntax is based on English syntax, and uses many of the same elements as Visual Basic for Applications (VBA) syntax. For example, a simple SQL statement that retrieves a list of last names for contacts whose first name is Mary might resemble this.
- VB.Net Advanced Tutorial
An example of how to use listboxes (as well as how to put images into buttons). This example lets you move items between lists. Rod Stephens, modified by Saul Greenberg: vbPlaysound: Illustrates how to play a wave file in Visual Basic. It works by declaring a function to the WIN32 API sndPlaySound. In this introduction to Microsoft Access Visual Basic for Applications programming, you will learn how to write your first code in Access VBA. In this example, let us access data in a DataGridView control using code. Take the following steps − Add a DataGridView control and a button in the form. Change the text of the button control to 'Fill'. Double click the button control to add the required code for the Click event of the button, as shown below −. Total Visual SourceBook is the most extensive professional source code library for Microsoft Access, Office, VBA, and Visual Basic 6.0. Written by our staff of Microsoft MVP's, authors, and experienced developers, much of this code can't be found anywhere else.
- VB.Net Useful Resources
- Selected Reading
Applications communicate with a database, firstly, to retrieve the data stored there and present it in a user-friendly way, and secondly, to update the database by inserting, modifying and deleting data.
Microsoft ActiveX Data Objects.Net (ADO.Net) is a model, a part of the .Net framework that is used by the .Net applications for retrieving, accessing and updating data.
Visual Basic Source Code Examples
ADO.Net Object Model
ADO.Net object model is nothing but the structured process flow through various components. The object model can be pictorially described as −
The data residing in a data store or database is retrieved through the data provider. Various components of the data provider retrieve data for the application and update data.
An application accesses data either through a dataset or a data reader.
Datasets store data in a disconnected cache and the application retrieves data from it.
Data readers provide data to the application in a read-only and forward-only mode.
Data Provider
A data provider is used for connecting to a database, executing commands and retrieving data, storing it in a dataset, reading the retrieved data and updating the database.
The data provider in ADO.Net consists of the following four objects −
| Sr.No. | Objects & Description |
|---|---|
| 1 | Connection This component is used to set up a connection with a data source. |
| 2 | Command A command is a SQL statement or a stored procedure used to retrieve, insert, delete or modify data in a data source. |
| 3 | DataReader Data reader is used to retrieve data from a data source in a read-only and forward-only mode. |
| 4 | DataAdapter This is integral to the working of ADO.Net since data is transferred to and from a database through a data adapter. It retrieves data from a database into a dataset and updates the database. When changes are made to the dataset, the changes in the database are actually done by the data adapter. |
There are following different types of data providers included in ADO.Net
The .Net Framework data provider for SQL Server - provides access to Microsoft SQL Server.
The .Net Framework data provider for OLE DB - provides access to data sources exposed by using OLE DB.
The .Net Framework data provider for ODBC - provides access to data sources exposed by ODBC.
The .Net Framework data provider for Oracle - provides access to Oracle data source.
The EntityClient provider - enables accessing data through Entity Data Model (EDM) applications.
DataSet
DataSet is an in-memory representation of data. It is a disconnected, cached set of records that are retrieved from a database. When a connection is established with the database, the data adapter creates a dataset and stores data in it. After the data is retrieved and stored in a dataset, the connection with the database is closed. This is called the 'disconnected architecture'. The dataset works as a virtual database containing tables, rows, and columns.
The following diagram shows the dataset object model −
The DataSet class is present in the System.Data namespace. The following table describes all the components of DataSet −
| Sr.No. | Components & Description |
|---|---|
| 1 | DataTableCollection It contains all the tables retrieved from the data source. |
| 2 | DataRelationCollection It contains relationships and the links between tables in a data set. |
| 3 | ExtendedProperties It contains additional information, like the SQL statement for retrieving data, time of retrieval, etc. |
| 4 | DataTable It represents a table in the DataTableCollection of a dataset. It consists of the DataRow and DataColumn objects. The DataTable objects are case-sensitive. |
| 5 | DataRelation It represents a relationship in the DataRelationshipCollection of the dataset. It is used to relate two DataTable objects to each other through the DataColumn objects. |
| 6 | DataRowCollection It contains all the rows in a DataTable. |
| 7 | DataView It represents a fixed customized view of a DataTable for sorting, filtering, searching, editing and navigation. |
| 8 | PrimaryKey It represents the column that uniquely identifies a row in a DataTable. |
| 9 | DataRow It represents a row in the DataTable. The DataRow object and its properties and methods are used to retrieve, evaluate, insert, delete, and update values in the DataTable. The NewRow method is used to create a new row and the Add method adds a row to the table. |
| 10 | DataColumnCollection It represents all the columns in a DataTable. |
| 11 | DataColumn It consists of the number of columns that comprise a DataTable. |
Connecting to a Database
The .Net Framework provides two types of Connection classes −
SqlConnection − designed for connecting to Microsoft SQL Server.
OleDbConnection − designed for connecting to a wide range of databases, like Microsoft Access and Oracle.
Example 1
We have a table stored in Microsoft SQL Server, named Customers, in a database named testDB. Please consult 'SQL Server' tutorial for creating databases and database tables in SQL Server.
Let us connect to this database. Take the following steps −
Select TOOLS → Connect to Database
Select a server name and the database name in the Add Connection dialog box.
M
Click on the Test Connection button to check if the connection succeeded.
Add a DataGridView on the form.
Click on the Choose Data Source combo box.
Click on the Add Project Data Source link.
This opens the Data Source Configuration Wizard.
Select Database as the data source type
Choose DataSet as the database model.
Choose the connection already set up.
Save the connection string.
Choose the database object, Customers table in our example, and click the Finish button.
Select the Preview Data link to see the data in the Results grid −
When the application is run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −
Example 2
In this example, let us access data in a DataGridView control using code. Take the following steps −
Add a DataGridView control and a button in the form.
Change the text of the button control to 'Fill'.
Double click the button control to add the required code for the Click event of the button, as shown below −
When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −
Clicking the Fill button displays the table on the data grid view control −
Creating Table, Columns and Rows
We have discussed that the DataSet components like DataTable, DataColumn and DataRow allow us to create tables, columns and rows, respectively.
The following example demonstrates the concept −
Example 3
So far, we have used tables and databases already existing in our computer. In this example, we will create a table, add columns, rows and data into it and display the table using a DataGridView object.
Take the following steps −
Add a DataGridView control and a button in the form.
Change the text of the button control to 'Fill'.
Add the following code in the code editor.
When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −
Clicking the Fill button displays the table on the data grid view control −
∟Introduction of VBScript - Visual Basic Scripting Edition
∟Using Visual Basic with Microsoft Access
This section provides tutorial example on how to add a Visual Basic code in Microsoft Access database to be executed as a macro on database tables.
As a comparison, this tutorial shows you how to write Visual Basic for Application(VBA) code and run it as a macro with Microsoft Access.
Microsoft Access is a Microsoft application that can be used to store and manage data in database tables.Microsoft Access also supports a macro module that allows you to write macro code with VBA language.
If you have Microsoft Access installed on your Windows system, you can follow the steps below to create a simple application in VBA within Microsoft Access.
1. Run Microsoft Access, and create a blank Access Database called vb_tutorial.mdb.
2. Click Insert > Module from the menu. The Microsoft Visual Basic window shows up.
3. Enter the following code into the empty code module:
4. Click File > Save from the menu. Enter 'Hello' as the module name and save it.
5. Click Run > Run Sub/UserForm from the menu. The macro selection dialog box shows up.
6. Select 'Main' macro, and click 'Run'. A dialog box shows up with the following message:
Congratulations. You have successfully written a VBA macro in Microsoft Access!
What happened here was:
- We have added a VBA macro called 'Hello' to our Access database, vb_tutorial.mdb.
- We have added a VBA procedure called 'Main' in the VBA macro. Access calls this procedure as a macro.
- The 'Main' procedure calls the 'MsgBox' function, which is a VBA built-in function that displays Windows dialog box with the specified text message.
- We ran the 'Main' procedure and got exactly what we expected.
Table of Contents
About This Book
►Introduction of VBScript - Visual Basic Scripting Edition
What Is VBScript?
Using VBScript with Internet Explorer 10 or Older
Using VBScript with Internet Explorer 11
Using VBScript with Internet Information Services
Using VBScript with WSH (Windows Script Host)
►Using Visual Basic with Microsoft Access
VBScript and Supporting Environments
Variant Data Type, Subtypes, and Literals
Arithmetic Operations
Numeric Comparison Operations and Logical Operations
String Operations - Concatenation and Comparison
Variable Declaration and Assignment Statement
Expression and Order of Operation Precedence
Statement Syntax and Statement Types
Array Data Type and Related Statements
Array References and Array Assignment Statements
Visual Basic Codes List
Conditional Statements - 'If ... Then' and 'Select Case'
Visual Basic Code Examples For Access Code
Loop Statements - 'For', 'While', and 'Do'
'Function' and 'Sub' Procedures
Built-in Functions
Inspecting Variables Received in Procedures
Error Handling Flag and the 'Err' Object
Regular Expression Pattern Match and Replacement
scrrun.dll - Scripting Runtime DLL Library
Creating Your Own Classes
IE Web Browser Supporting VBScript
IIS ASP Server Supporting VBScript
WSH (Windows Script Host)
References
Full Version in PDF/EPUB