VB.NET ASP.NET Table Load From SqlDataReader Source Code Example
VB.NET ASP.NET Table Load From SqlDataReader Source Code Examples
Purpose: – Illustrates using DataTable loaded from SqlDataReader in VB.NET ASP.NET.
Prerequistes:
- Install Visual Web Developer 2008
- 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.
Instructions:
- Use Visual Web Developer 2008
- 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 “ADONET”
- Right-click project name in solution explorer;
- add new folder;
- name of folder could be: ADONET
- Add Web Form Named TableLoadFromSqlDataReader to ADONET folder
- Right-click ADONET folder;
- add new item;
- Select Web Form
- Check place code behind in separate file
- Web Form name could be TableLoadFromSqlDataReader
- Click on copy code in code below to copy code into web form TableLoadFromSqlDataReader.aspx
- Click on copy code in second set of code below to copy code into code-behind TableLoadFromSqlDataReader.aspx.vb
- Right-click on TableLoadFromSqlDataReader.aspx in solution explorer and select view in browser
Step 1: Click on Copy Code to Cut-n-paste code into TableLoadFromSqlDataReader.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="TableLoadFromSqlDataReader.aspx.vb" Inherits="ADONET_TableLoadFromSqlDataReader" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </div> </form> </body> </html> |
Step 2: Click on Copy Code to Cut-n-paste code into TableLoadFromSqlDataReader.aspx.vb
Imports System Imports System.Data Imports System.Data.SqlClient Partial Class ADONET_TableLoadFromSqlDataReader Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim con As SqlConnection = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI") Dim cmd As New SqlCommand() cmd.CommandText = "SELECT EmployeeID, FirstName + ' ' + LastName as FullName FROM Employees" cmd.Connection = con 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("EmployeeID") 'declaring a column named EmployeeID 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("FullName") FullName.DataType = System.Type.GetType("System.String") Table1.Columns.Add(FullName) Try con.Open() Dim reader As SqlDataReader = cmd.ExecuteReader '(CommandBehavior.SingleRow) While reader.Read() Row1 = Table1.NewRow() 'declaring a new row Row1.Item("EmployeeID") = reader.GetInt32(0) 'filling the row with values. Item property is used to set the field value. Row1.Item("FullName") = reader.GetString(1) 'filling the row with values. adding FullName Table1.Rows.Add(Row1) End While reader.Close() Finally con.Close() End Try 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
<connectionTableLoadFromSqlDataReaders> <add name="Northwind_ConnectionTableLoadFromSqlDataReader" connectionTableLoadFromSqlDataReader="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" /> <add name="Pubs_ConnectionTableLoadFromSqlDataReader" connectionTableLoadFromSqlDataReader="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" /> </connectionTableLoadFromSqlDataReaders> |