Hi all,
I'm trying to develop an ASP.NET 2.0 website and am running into some
real problems with what I thought would be a relatively simple thing
to do.
In a nutshell, I'm stuck on trying to display data in a "GridView"
which is tied to an "ObjectDataSource".
In turn, this ObjectDatasource gets it's data from a strongly-typed
business object within my code.
I'm also trying to use custom paging, so my class has correctly
implemented methods to return the total count of records, and also the
method which retrieves the actual page of records is accepting two
parameters for the startRowIndex and maximumRows. I do NOT need any
sorting capability for this gridview, nor do I need any updating,
deleting, selecting functionality, either. I simply need to display
all the relevant records from the database (via my object), paged
(using SQL2005's ROW_NUMBER style paging).
I have this working beautifully when I use a strongly-typed dataset
for the ObjectDatasource's data, however, when I switch to using a
"business object", the Gridview doesn't seem to want to display any
data.
I can step through my code, and confirm that I am retrieving some
records from the database, which is then "returned" from the
"selectmethod" function call as a Generic List (ie. List(of T)).
Below, I've pasted some code that highlights my problem (I re-did the
code to focus on this one problem - in my real code, my DAL and BLL
are separate, whereas here they're in the same class, just for
brevity's sake). If your interested, you can download the entire
zipped up webproject (about 580kb) from here:
http://putstuff.putfile.com/17957/6448935
Has anyone encountered this problem before?? Does anyone know what
I'm doing wrong?? (I'm convinved it's something really simple that
I'm missing).
Any/all replies are greatly appreciated.
Regards,
Greg
The database has one table, as follows:
CREATE TABLE [dbo].[TestTable] (
[UniqueID] [uniqueidentifier] NOT NULL ,
[Name] [varchar] (50) NOT NULL ,
[BirthDate] [datetime] NOT NULL ,
[Comment] [varchar] (100) NOT NULL
)
There's 2 stored procedures, as follows:
ALTER PROCEDURE dbo.usp_GetTestTable
(
@startRowIndex int,
@maximumRows int
)
AS
SELECT UniqueID, [Name], BirthDate, Comment
FROM
(SELECT UniqueID, [Name], BirthDate, Comment, ROW_NUMBER()
OVER(ORDER BY BirthDate) AS RowNum
FROM TestTable
) AS TestTableList
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex +
@maximumRows) - 1
and:
ALTER PROCEDURE dbo.usp_GetTestTableCount
AS
SELECT
COUNT(*)
FROM
TestTable
The "custom class" is defined as follows:
Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports system.data
Imports System.Data.Sql
Imports system.Data.SqlClient
'
'
'
Public Class TestTableClass
'
'
Private m_UniqueID As String = ""
Private m_Name As String = ""
Private m_BirthDate As DateTime = Nothing
Private m_Comment As String = ""
'
'
Public Property UniqueID() As String
Get
Return m_UniqueID
End Get
Set(ByVal value As String)
m_UniqueID = value
End Set
End Property
'
Public Property Name() As String
Get
Return m_Name
End Get
Set(ByVal value As String)
m_Name = value
End Set
End Property
'
Public Property BirthDate() As DateTime
Get
Return m_BirthDate
End Get
Set(ByVal value As DateTime)
m_BirthDate = value
End Set
End Property
'
Public Property Comment() As String
Get
Return m_Comment
End Get
Set(ByVal value As String)
m_Comment = value
End Set
End Property
'
'
'
Public Function GetTestTableCount() As Integer
'
Dim oSQLConn As SqlConnection
Dim oSQLCmd As SqlCommand
Dim intResult As Integer = 0
'
oSQLConn = New
SqlConnection(ConfigurationManager.ConnectionStrin gs("SiteDB").ToString)
oSQLConn.Open()
'
oSQLCmd = New SqlCommand
oSQLCmd.Connection = oSQLConn
oSQLCmd.CommandType = CommandType.StoredProcedure
oSQLCmd.CommandText = "usp_GetTestTableCount"
'
intResult = oSQLCmd.ExecuteScalar
'
oSQLConn.Close()
'
End Function
'
'
Public Function GetTestTable(ByVal startRowIndex As Integer, ByVal
maximumRows As Integer) As List(Of TestTableClass)
'
Dim oSQLConn As SqlConnection
Dim oSQLCmd As SqlCommand
Dim oSQLParam As SqlParameter
Dim oDR As SqlDataReader
Dim mTTC As TestTableClass
Dim mColl As List(Of TestTableClass) = New List(Of
TestTableClass)
Dim intResult As Integer = 0
'
oSQLConn = New
SqlConnection(ConfigurationManager.ConnectionStrin gs("SiteDB").ToString)
oSQLConn.Open()
'
oSQLCmd = New SqlCommand
oSQLCmd.Connection = oSQLConn
oSQLCmd.CommandType = CommandType.StoredProcedure
oSQLCmd.CommandText = "usp_GetTestTable"
'
oSQLParam = New SqlParameter
oSQLParam.SqlDbType = SqlDbType.Int
oSQLParam.Direction = ParameterDirection.Input
oSQLParam.ParameterName = "startRowIndex"
oSQLParam.Value = startRowIndex
oSQLCmd.Parameters.Add(oSQLParam)
'
oSQLParam = New SqlParameter
oSQLParam.SqlDbType = SqlDbType.Int
oSQLParam.Direction = ParameterDirection.Input
oSQLParam.ParameterName = "maximumRows"
oSQLParam.Value = maximumRows
oSQLCmd.Parameters.Add(oSQLParam)
'
oDR = oSQLCmd.ExecuteReader()
'
While oDR.Read
'
mTTC = New TestTableClass
mTTC.UniqueID = oDR.Item("UniqueID").ToString
mTTC.Name = oDR.Item("Name").ToString
mTTC.BirthDate = oDR.Item("BirthDate")
mTTC.Comment = oDR.Item("Comment").ToString
'
mColl.Add(mTTC)
'
End While
'
oSQLConn.Close()
'
Return mColl
'
End Function
'
'
End Class
And the "Default.aspx" page (which has nothing in it's code-behind) is
defined as follows:
<%@ Page Language="VB" AutoEventWireup="false"
CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="TestTableClass" SelectCountMethod="GetTestTableCount"
SelectMethod="GetTestTable" EnablePaging="True">
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="UniqueID"
HeaderText="UniqueID" SortExpression="UniqueID" />
<asp:BoundField DataField="BirthDate"
HeaderText="BirthDate" SortExpression="BirthDate" />
<asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
<asp:BoundField DataField="Comment"
HeaderText="Comment" SortExpression="Comment" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
