ASP-NET Course: ASP.NET Database Connection

iTech College - Internet Technology, Online Education, E-Learning, Technician Training, Tech Tutorials, Distance Education, Virtual Learning

	HTML
	JavaScript
	CSS
	ASP
	PHP
	JSP
	C++
	ASP.NET
	SQL
	AJAX
	FTP
	XML

courses > ASP-NET > ASP.NET Database Connection

ASP.NET Database Connection

First, for creating a database connection, we need to import the "System.Data.OleDb" namespace to work with Microsoft Access and other databases:

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbcon,q,dbcom,dbread

Open database connection:

dbcon=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
data source=" & server.mappath("northwind.mdb"))
dbcon.Open()

Use a SQL query to retrieve records from a table:

q="SELECT * FROM customers"
dbcom=New OleDbCommand(q,dbcon)

Read records from a table:

dbread=dbcom.ExecuteReader()

Then we need to bind the DataReader to a Repeater control:

customers.DataSource=dbread
customers.DataBind()

Close the DataReader and data base connection. And end the sub and the script:

dbread.Close()
dbcon.Close()
end sub
</script>

See the full example:

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbcon,q,dbcom,dbread

dbcon=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
data source=" & server.mappath("northwind.mdb"))
dbcon.Open()

q="SELECT * FROM customers"
dbcom=New OleDbCommand(q,dbcon)
dbread=dbcom.ExecuteReader()

customers.DataSource=dbread
customers.DataBind()

dbread.Close()
dbcon.Close()
end sub
</script>

To display records to the web page, we need a form and a Repeater control:

<html><body>
<form runat="server">

<asp:Repeater id="customers" runat="server">

There are three Templates: HeaderTemplate, FooterTemplate and ItemTemplate. Let's see how to use them with a HTML table:

Print the header of the table once inside HeaderTemplate:

<HeaderTemplate>
<table border="1">
<tr>
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>

We need ItemTemplate to Repeat printing mutiple records from the database table into the HTML table:

<ItemTemplate>
<tr>
<td><%#Container.DataItem("companyname")%></td>
<td><%#Container.DataItem("contactname")%></td>
<td><%#Container.DataItem("address")%></td>
<td><%#Container.DataItem("city")%></td>
</tr>
</ItemTemplate>

Print the footer of the table once inside FooterTemplate: