| ADO.NET and SELECT |
|
Objectives |
| Discussion |
ADO.NET (ActiveX Data Objects)
Steps to Connect to Database and Display Data
OR ... Use VS graphical tools and/or wizards which generate the code for you
What is a Connection String?
Displaying Data
| Demonstration |
In this demo we will connect to the Contacts database that you previously created and display it in a datagrid. In the code below for the DataSource parameter in the connection string you must use the path to your database. In the code below, the database was stored in the bin directory of the program. Therefore we used application.startuppath to provide the path. Make sure that your database is in the bin directory of your project and is called contacts.mdb; otherwise change the code to match the location and name of your database.
1. Create a new project called Unit 9. Add a button (btnDisplay) and a DataGridView. Leave the name of the DataGridView as DataGridView1. Copy and paste the following heavily commented code into the click event for btnDisplay.
'sConn defines the connection string. There are many parts of a string.
'You must have the provider and the datasource.
'The primary items in a connection string include:
'The Provider defines the type of database used by the connection.
'The User ID and Password define the authentication information required by
'the provider. For Access databases, the default User ID is "Admin"
'The database in this example has no password.
'The data source defines the file containing the database.
'The mode defines how the database will be shared among other connected users.
'In this example its value is set to Share Deny None which means
'that multiple users can read and write data to the database while
'the connection is open.
Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" 'for Access 2007 databases
'Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" 'for Access 2003 databases
'sConn = sConn & "Password='';User ID=Admin;" 'not necessary in this example
'sConn = sConn & "Jet OLEDB:Database Password= passwordhere;" 'This format for 2007?
'sConn = sConn & "Mode=Share Deny None;" 'not necessary in this example
sConn = sConn & "Data Source= " & Application.StartupPath & "\contacts.accdb"
Try
Dim conX As New OleDb.OleDbConnection(sConn) 'creates the connection
Dim dsX As New DataSet() 'creates the dataset
Dim sSQL As String 'stores the SQL string
sSQL = "SELECT * FROM Contacts" 'SQL statement for retrieving all info from database
Dim daX As New OleDb.OleDbDataAdapter(sSQL, conX) 'creates a dataAdaptor
daX.Fill(dsX) 'Fills the dataset
'Next line uses a DATAGRID >>> older grid object >>> We will use a DATAGRIDVIEW
'DataGrid1.DataSource = dsX.Tables(0) 'Links the datagrid to the dataset
'DataGrid1.Refresh() 'Refreshes the grid to display the data NOT NECESSARY
'Next line uses a DATAGRIDVIEW >>> newer grid object >>> We will use this
DataGridView1.DataSource = dsX.Tables(0) 'Links the datagridview to the dataset
conX.Close() 'Closes the connection
Catch er As Exception
MessageBox.Show(er.Message)
End Try
2. After you have copied the code, study it in VS. Test it to make sure it works. If it doesn't work check the error message. There is a good chance that your path (data source) is not right.
3. Study the code carefully.
4. Study the code again, just to make sure you understand it.
| Exercises |
1. Write the most simple yet valid connection string to connect to an Access database with a path "C:\mydatabase.mdb".
2. In the code above, what line adds the seleted data to the dataset.
3. What information do you need to pass into the DataAdaptor object?
4. What is the purpose of the datagridview? Are there other things that we can use instead of the datagridview?
5. What should you do after you are through using the connection?
6. Add a textbox and button to the program that you created in the demo. Allow the user to enter a SQL statement in the textbox and then click the button to display the selected data in the datagrid.
| Links & Help |