This tutorial will show you how to display data using the .NET GridView Control, stored procedures, ASP.NET 2.0 and VB.NET
Querying an SQL database with stored procedures using C# .NET is easy to do.
First, you will need to import the System.Data.SqlClient namespace.
The System.Data.SqlClient namespace contains the SqlCommand and SqlConnection classes that we need in order to connect to our database and to send an SQL command to it.
|
Imports System.Data.SqlClient |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
We'll put our code in the Page_Load() event.
When the Page_Load() event fires, a new SqlCommand object is instantiated with our connection string and our stored procedure name. We add the parameters needed for the stored procedure by using our SqlCommand object's Parameters.Add() method.
Afterwards, we will attempt to connect using the Open() method of our cmd.Connection object. Once it is connected we will attempt to execute the stored procedure we specified earlier (in this example CustOrderHist stored procedure in the Northwind db).
If all goes well, we will have the results of our SQL query assigned to the gvwExample's DataSource property. Now all we have to do is call the DataBind() method of our gvwExample to bind the data to the control. The data is now ready to be displayed.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
Dim cmd As SqlCommand = New SqlCommand("CustOrderHist", New SqlConnection("Server=localhost;Database=Northwind;Trusted_Connection=True;"))
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("CustomerID", txtCustID.Text)
cmd.Connection.Open()
gvwExample.DataSource = cmd.ExecuteReader()
gvwExample.DataBind()
cmd.Connection.Close()
cmd.Connection.Dispose()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try |
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
We have to add a few tags on the front end of the .aspx page to place where we want the GridView control to display its bound data. We also specify what part of the data from the data set we would like to display. The front end .aspx page looks something like this:
<table width="600" border="0" align="center" cellpadding="5" cellspacing="1" bgcolor="#cccccc">
<tr>
<td width="100" align="right" bgcolor="#eeeeee" class="header1"> Customer Data Using Stored Procedures:</td>
<td align="center" bgcolor="#FFFFFF">
<asp:GridView ID="gvwExample" runat="server" AutoGenerateColumns="False" CssClass="basix" >
<columns>
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="Total" HeaderText="Total" />
</columns>
</asp:GridView>
<br />
Customer ID:
<asp:TextBox ID="txtCustID" runat="server" Width="42px">ALFKI</asp:TextBox>
Order ID:<asp:TextBox ID="txtOrderID" runat="server" Width="43px">10256</asp:TextBox><br />
<asp:Button ID="btnSubmit" runat="server" Text="Go" />
<br />
<asp:label ID="lblStatus" runat="server"></asp:label></td>
</tr>
</table>
|
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
The flow for the code behind page is as follows.
Imports System.Data.SqlClient
Partial Public Class _Default : Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
Dim cmd As SqlCommand = New SqlCommand("CustOrderHist", New SqlConnection("Server=localhost;Database=Northwind;Trusted_Connection=True;"))
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("CustomerID", txtCustID.Text)
cmd.Connection.Open()
gvwExample.DataSource = cmd.ExecuteReader()
gvwExample.DataBind()
cmd.Connection.Close()
cmd.Connection.Dispose()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
End Sub |
Download the Full Working Version of this Project written with Visual Studio.NET VB.NET 2005 Here!
Looking for the C#.NET 2005 Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!