Get data from a database using ASP.NET

[Kings]

Introduction
I already showed you how to get data from a database using ASP, now I'm going to show you how to do it using ASP.NET
In this tutorial I'm going to use a Access Database.

First make a connection
Like I said in my other tutorial, we first need to make a connection to our database.


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
sub Page_Load(sender as Object, e as EventArgs)
Dim connString as String
connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & _
"C:\Inetpub\wwwroot\yourdb.mdb;"

Dim objConnection as OleDbConnection
objConnection = New OleDbConnection(connString)
objConnection.Open() 'open the connection

'rest of the code


We have opened our connection, and we can now use the connection. We included System.Data.OleDb so we can use OleDbConnection.

Selecting Data

We want to select data now, from our table. We're going to use the following to do that.


'Specify our Query
Dim strQuery as String = "SELECT Row1, Row2 FROM Table1"

'Create the Command object
Dim objCommand as OleDbCommand
objCommand = New OleDbCommand(strQuery, objConnection)


Now we have opened a connection to the database, and selected our data, the only thing left to do is display it.

Setting Datareader

Displaying database data through ASP.NET is different from ASP. In ASP you would loop until objRS.EOF was True. Something like this;


While NOT objRS.EOF
'do code
objRS.MoveNext
Loop


In ASP.NET it's done differently. It uses a datareader to read all the data, and then display it in a datagrid, or loop through the datareader.


' Set an OleDbDataReader to the results
Dim objDataReader as OleDbDataReader
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)


Now our Datareader has our data, and we're ready to display it.

Display Data

Now we're going to display the data, using a datagrid.


'Put the data in our datagrid
dgDB.DataSource = objDataReader
dgDB.DataBind()

'Close the datareader/db connection
objDataReader.Close()

'Close our sub
end sub
</script>

<asp:DataGrid id="dgDB" runat="server" />


That was it! We have displayed out data in a datagrid.
But what if you want to check the data, and make your own table?

Looping Through The Datareader

You can also loop through the datareader, and check values, or make your own table.


While objDataReader.Read()
'your code
' e.g = objDataReader("Row1")
End While


Complete Code


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
sub Page_Load(sender as Object, e as EventArgs)
Dim connString as String
connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & _
"C:\Inetpub\wwwroot\yourdb.mdb;"

Dim objConnection as OleDbConnection
objConnection = New OleDbConnection(connString)
objConnection.Open() 'open the connection


'Specify our Query
Dim strQuery as String = "SELECT Row1, Row2 FROM Table1"

'Create the Command object
Dim objCommand as OleDbCommand
objCommand = New OleDbCommand(strQuery, objConnection)


' Set an OleDbDataReader to the results
Dim objDataReader as OleDbDataReader
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

'Put the data in our datagrid
dgDB.DataSource = objDataReader
dgDB.DataBind()

'Close the datareader/db connection
objDataReader.Close()

'Close our sub
end sub
</script>

<asp:DataGrid id="dgDB" runat="server" />


That was it!