Access DB DataReader To Gridview
Access DB DataReader To Gridview
Purpose: – Illustrates using Acces DB DataReader Gridview in VB.NET ASP.NET.
Prerequistes:
- Install Visual Web Developer 2010
- Install SQL Server Express
- Download Northwind and Pubs Databases
- Attach Northwind Database to Databases in Sql Express
- Attach pubs Database to Databases in Sql Express
Notes:
- You can build your own library of syntax examples by using same web site over and over and just add new web forms to it.
- This example uses MS Access 2010 to create the Contacts DB
Instructions:
- Use Visual Web Developer 2010
- Create new web site;
- Click File/New Web Site
- Select ASP.NET Website Template
- Select Visual Basic for Language
- name of Web Site could be VBNET_ASPNET_Syntax.
- Add New folder named “Database_ADONET”
- Right-click project name in solution explorer;
- add new folder;
- name of folder could be: Database_ADONET
- Add Web Form Named AccessDBDataReaderToGridview to Database_ADONET folder
- Right-click Database_ADONET folder;
- add new item;
- Select Web Form
- Check place code behind in separate file
- Web Form name could be AccessDBDataReaderToGridview
- Click on copy code in code below to copy code into web form AccessDBDataReaderToGridview.aspx
- Click on copy code in second set of code below to copy code into code-behind AccessDBDataReaderToGridview.aspx.vb
- Right-click on AccessDBDataReaderToGridview.aspx in solution explorer and select view in browser
Step 1: Click on Copy Code to Cut-n-paste code into AccessDBDataReaderToGridview.aspx
< %@ Page Language="VB" AutoEventWireup="false" CodeFile="AccessDBDataReaderToGridview.aspx.vb" Inherits="ADONET_AccessDBDataReaderToGridview" %> < !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <h2>Populate GridView1 with Reader</h2> <asp:gridview ID="GridView1" runat="server"> </asp:gridview> </div> </form> </body> </html> |
Step 2: Click on Copy Code to Cut-n-paste code into AccessDBDataReaderToGridview.aspx.vb
Imports System.Data Imports System.Data.OleDb Partial Class ADONET_AccessDBDataReaderToGridview Inherits System.Web.UI.Page Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ' create a connection string Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Contacts.accdb" Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString Dim sql As String = "SELECT * FROM Contacts" Dim cmd As New OleDbCommand(sql, myConnection) myConnection.Open() Dim reader As OleDbDataReader reader = cmd.ExecuteReader() Dim Table1 As DataTable Table1 = New DataTable("Employees") 'creating a table named Employees Dim Row1 As DataRow 'declaring row for the table Dim EmployeeID As DataColumn = New DataColumn("ContactID") 'declaring a column named ContactID EmployeeID.DataType = System.Type.GetType("System.Int32") 'setting the datatype for the column Table1.Columns.Add(EmployeeID) 'adding the column to table Dim FullName As DataColumn = New DataColumn("LastName") FullName.DataType = System.Type.GetType("System.String") Table1.Columns.Add(FullName) While reader.Read() Row1 = Table1.NewRow() 'declaring a new row Row1.Item("ContactID") = reader.GetInt32(0) 'filling the row with values. Item property is used to set the field value. Row1.Item("LastName") = reader.GetString(2) 'filling the row with values. adding LastName Table1.Rows.Add(Row1) End While reader.Close() myConnection.Close() GridView1.DataSource = Table1 GridView1.DataBind() End Sub End Class |
Step 3: Click on Copy Code to Cut-n-paste code into web.config right after the appSettings section
<connectionstrings> <add name="Northwind_ConnectionString" connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" /> <add name="Pubs_ConnectionString" connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" /> </connectionstrings> |