Populating a gridview (.net 2.0) using objectdatasource

Discussion in 'ASP .Net' started by Julia B, Dec 17, 2008.

  1. Julia B

    Julia B Guest

    Hi, I've got a gridview populated using an object datasource. The object
    datasource calls on a function within a business layer class that returns a
    dataset. The business layer class calls on another function in a database
    connection class that returns a dataset, using a stored procedure. This all
    works fine. However I want now to refine it and reset the gridview based on
    specific values, ie. not showing all data, but showing data for a particular
    user. I can change the stored procedure to add a parameter, but how do I pass
    the parameter's value to the stored procedure using the objectdatasource?

    Here's what I've got so far:

    <asp:GridView ID="dgAccs" runat="server" AllowSorting="True"
    AutoGenerateSelectButton="True" DataSourceID="dgDataSource">
    </asp:GridView>
    <asp:ObjectDataSource ID="dgDataSource" runat="server"
    SelectMethod="FillDataGrid"
    TypeName="RC.RC_Web_AccountMonitor.CommonUtilities">
    <SelectParameters>
    <asp:parameter DefaultValue="sp_DepartmentList" Name="spName" />
    </SelectParameters>
    </asp:ObjectDataSource>

    Namespace RC_Web_AccountMonitor
    Public Class CommonUtilities
    Public Function FillDataGrid(ByVal spName As String) As DataSet

    Dim dsDataGrid As DataSet
    'this function returns a dataset that fills an unparametered datagrid
    ‘#### note I realise I will have to add a parameter here – but still not
    sure how to pass the parameter value from the objectdatasource
    With New DBConnection
    .ClearParameters()
    dsDataGrid = .GetDatasetFromSP(spName)
    End With
    Return dsDataGrid
    End Function

    End Class
    End Namespace

    Public Class DBConnection
    Private _ParamList As SqlParameterCollection
    Private _MyComm As New SqlCommand
    Private _MyTrans As SqlTransaction
    Private _ConnectionStr As String =
    ConfigurationManager.ConnectionStrings("Conn").ToString

    'shared connection
    Dim conn As New SqlConnection(_ConnectionStr)

    Public Sub New()

    End Sub

    Public Function GetDatasetFromSP(ByVal SPName As String) As DataSet
    Dim Myds As New DataSet
    Dim MyAdapter As New SqlDataAdapter(_MyComm)
    _MyComm.CommandType = CommandType.StoredProcedure
    _MyComm.CommandText = SPName
    _MyComm.Connection = conn
    Try
    MyAdapter.Fill(Myds, "Results")
    Catch ex As Exception
    Dim errorMessage As String = ex.GetType.ToString & " " & ex.Message.ToString
    System.Web.HttpContext.Current.Session("DBError") = "True"
    System.Web.HttpContext.Current.Session("DBEx") = errorMessage
    Throw ex
    Finally
    End Try
    Return Myds
    End Function

    Public Sub ClearParameters()
    _MyComm.Parameters.Clear()
    End Sub

    Public Sub AddParameter(ByVal ParamName As String, ByVal paramType As
    Data.SqlDbType, ByVal Paramlength As Int16, ByVal ParamValue As String)
    _MyComm.Parameters.Add(ParamName, paramType, Paramlength)
    If ParamValue = Nothing Or ParamValue = "00:00:00" Or ParamValue = "" Then
    _MyComm.Parameters(ParamName).Value = DBNull.Value
    Else
    _MyComm.Parameters(ParamName).Value = ParamValue
    End If
    End Sub

    End Class

    Thanks in advance
    Julia
     
    Julia B, Dec 17, 2008
    #1
    1. Advertising

  2. You need to add a parameter to whatever the object datasource calls, such as
    your business layer object. Then add a select parameter to your object
    datasource. You can set the parameter values for the datasource
    programatically so you could have a checkbox or dropdownlist that, when
    triggering a server side change or click event, changes the DefaultValue of
    a particular SelectParemter of the datasource to whatever value you need it
    to be then rebind your grid.

    Hope this helps,
    Mark Fitzpatrick

    "Julia B" <> wrote in message
    news:...
    > Hi, I've got a gridview populated using an object datasource. The object
    > datasource calls on a function within a business layer class that returns
    > a
    > dataset. The business layer class calls on another function in a database
    > connection class that returns a dataset, using a stored procedure. This
    > all
    > works fine. However I want now to refine it and reset the gridview based
    > on
    > specific values, ie. not showing all data, but showing data for a
    > particular
    > user. I can change the stored procedure to add a parameter, but how do I
    > pass
    > the parameter's value to the stored procedure using the objectdatasource?
    >
    > Here's what I've got so far:
    >
    > <asp:GridView ID="dgAccs" runat="server" AllowSorting="True"
    > AutoGenerateSelectButton="True" DataSourceID="dgDataSource">
    > </asp:GridView>
    > <asp:ObjectDataSource ID="dgDataSource" runat="server"
    > SelectMethod="FillDataGrid"
    > TypeName="RC.RC_Web_AccountMonitor.CommonUtilities">
    > <SelectParameters>
    > <asp:parameter DefaultValue="sp_DepartmentList" Name="spName" />
    > </SelectParameters>
    > </asp:ObjectDataSource>
    >
    > Namespace RC_Web_AccountMonitor
    > Public Class CommonUtilities
    > Public Function FillDataGrid(ByVal spName As String) As DataSet
    >
    > Dim dsDataGrid As DataSet
    > 'this function returns a dataset that fills an unparametered datagrid
    > ‘#### note I realise I will have to add a parameter here – but still not
    > sure how to pass the parameter value from the objectdatasource
    > With New DBConnection
    > .ClearParameters()
    > dsDataGrid = .GetDatasetFromSP(spName)
    > End With
    > Return dsDataGrid
    > End Function
    >
    > End Class
    > End Namespace
    >
    > Public Class DBConnection
    > Private _ParamList As SqlParameterCollection
    > Private _MyComm As New SqlCommand
    > Private _MyTrans As SqlTransaction
    > Private _ConnectionStr As String =
    > ConfigurationManager.ConnectionStrings("Conn").ToString
    >
    > 'shared connection
    > Dim conn As New SqlConnection(_ConnectionStr)
    >
    > Public Sub New()
    >
    > End Sub
    >
    > Public Function GetDatasetFromSP(ByVal SPName As String) As DataSet
    > Dim Myds As New DataSet
    > Dim MyAdapter As New SqlDataAdapter(_MyComm)
    > _MyComm.CommandType = CommandType.StoredProcedure
    > _MyComm.CommandText = SPName
    > _MyComm.Connection = conn
    > Try
    > MyAdapter.Fill(Myds, "Results")
    > Catch ex As Exception
    > Dim errorMessage As String = ex.GetType.ToString & " " &
    > ex.Message.ToString
    > System.Web.HttpContext.Current.Session("DBError") = "True"
    > System.Web.HttpContext.Current.Session("DBEx") = errorMessage
    > Throw ex
    > Finally
    > End Try
    > Return Myds
    > End Function
    >
    > Public Sub ClearParameters()
    > _MyComm.Parameters.Clear()
    > End Sub
    >
    > Public Sub AddParameter(ByVal ParamName As String, ByVal paramType As
    > Data.SqlDbType, ByVal Paramlength As Int16, ByVal ParamValue As String)
    > _MyComm.Parameters.Add(ParamName, paramType, Paramlength)
    > If ParamValue = Nothing Or ParamValue = "00:00:00" Or ParamValue = "" Then
    > _MyComm.Parameters(ParamName).Value = DBNull.Value
    > Else
    > _MyComm.Parameters(ParamName).Value = ParamValue
    > End If
    > End Sub
    >
    > End Class
    >
    > Thanks in advance
    > Julia
     
    Mark Fitzpatrick, Dec 17, 2008
    #2
    1. Advertising

  3. Julia B

    Julia B Guest

    Mark, thanks. I've managed to get it working. I didn't realise that you could
    create a select parameter in an object datasource and get it to reference the
    form's query string which is what I needed it to do. My gridview's select
    query is now based on the forms' query string.

    Julia

    "Mark Fitzpatrick" wrote:

    > You need to add a parameter to whatever the object datasource calls, such as
    > your business layer object. Then add a select parameter to your object
    > datasource. You can set the parameter values for the datasource
    > programatically so you could have a checkbox or dropdownlist that, when
    > triggering a server side change or click event, changes the DefaultValue of
    > a particular SelectParemter of the datasource to whatever value you need it
    > to be then rebind your grid.
    >
    > Hope this helps,
    > Mark Fitzpatrick
    >
    > "Julia B" <> wrote in message
    > news:...
    > > Hi, I've got a gridview populated using an object datasource. The object
    > > datasource calls on a function within a business layer class that returns
    > > a
    > > dataset. The business layer class calls on another function in a database
    > > connection class that returns a dataset, using a stored procedure. This
    > > all
    > > works fine. However I want now to refine it and reset the gridview based
    > > on
    > > specific values, ie. not showing all data, but showing data for a
    > > particular
    > > user. I can change the stored procedure to add a parameter, but how do I
    > > pass
    > > the parameter's value to the stored procedure using the objectdatasource?
    > >
    > > Here's what I've got so far:
    > >
    > > <asp:GridView ID="dgAccs" runat="server" AllowSorting="True"
    > > AutoGenerateSelectButton="True" DataSourceID="dgDataSource">
    > > </asp:GridView>
    > > <asp:ObjectDataSource ID="dgDataSource" runat="server"
    > > SelectMethod="FillDataGrid"
    > > TypeName="RC.RC_Web_AccountMonitor.CommonUtilities">
    > > <SelectParameters>
    > > <asp:parameter DefaultValue="sp_DepartmentList" Name="spName" />
    > > </SelectParameters>
    > > </asp:ObjectDataSource>
    > >
    > > Namespace RC_Web_AccountMonitor
    > > Public Class CommonUtilities
    > > Public Function FillDataGrid(ByVal spName As String) As DataSet
    > >
    > > Dim dsDataGrid As DataSet
    > > 'this function returns a dataset that fills an unparametered datagrid
    > > ‘#### note I realise I will have to add a parameter here – but still not
    > > sure how to pass the parameter value from the objectdatasource
    > > With New DBConnection
    > > .ClearParameters()
    > > dsDataGrid = .GetDatasetFromSP(spName)
    > > End With
    > > Return dsDataGrid
    > > End Function
    > >
    > > End Class
    > > End Namespace
    > >
    > > Public Class DBConnection
    > > Private _ParamList As SqlParameterCollection
    > > Private _MyComm As New SqlCommand
    > > Private _MyTrans As SqlTransaction
    > > Private _ConnectionStr As String =
    > > ConfigurationManager.ConnectionStrings("Conn").ToString
    > >
    > > 'shared connection
    > > Dim conn As New SqlConnection(_ConnectionStr)
    > >
    > > Public Sub New()
    > >
    > > End Sub
    > >
    > > Public Function GetDatasetFromSP(ByVal SPName As String) As DataSet
    > > Dim Myds As New DataSet
    > > Dim MyAdapter As New SqlDataAdapter(_MyComm)
    > > _MyComm.CommandType = CommandType.StoredProcedure
    > > _MyComm.CommandText = SPName
    > > _MyComm.Connection = conn
    > > Try
    > > MyAdapter.Fill(Myds, "Results")
    > > Catch ex As Exception
    > > Dim errorMessage As String = ex.GetType.ToString & " " &
    > > ex.Message.ToString
    > > System.Web.HttpContext.Current.Session("DBError") = "True"
    > > System.Web.HttpContext.Current.Session("DBEx") = errorMessage
    > > Throw ex
    > > Finally
    > > End Try
    > > Return Myds
    > > End Function
    > >
    > > Public Sub ClearParameters()
    > > _MyComm.Parameters.Clear()
    > > End Sub
    > >
    > > Public Sub AddParameter(ByVal ParamName As String, ByVal paramType As
    > > Data.SqlDbType, ByVal Paramlength As Int16, ByVal ParamValue As String)
    > > _MyComm.Parameters.Add(ParamName, paramType, Paramlength)
    > > If ParamValue = Nothing Or ParamValue = "00:00:00" Or ParamValue = "" Then
    > > _MyComm.Parameters(ParamName).Value = DBNull.Value
    > > Else
    > > _MyComm.Parameters(ParamName).Value = ParamValue
    > > End If
    > > End Sub
    > >
    > > End Class
    > >
    > > Thanks in advance
    > > Julia

    >
    >
     
    Julia B, Dec 18, 2008
    #3
  4. Julia B

    SteveT Guest

    On Dec 17, 9:32 am, Julia B <> wrote:
    > Hi, I've got a gridview populated using an object datasource. The object
    > datasource calls on a function within a business layer class that returns a
    > dataset. The business layer class calls on another function in a database
    > connection class that returns a dataset, using a stored procedure. This all
    > works fine. However I want now to refine it and reset the gridview based on
    > specific values, ie. not showing all data, but showing data for a particular
    > user. I can change the stored procedure to add a parameter, but how do I pass
    > the parameter's value to the stored procedure using the objectdatasource?



    Another alternative is to use the filter parameter in the
    ObjectDataSource. You can then use a control, like a drop-down, to
    let the user choose the filter.

    Steve T.
     
    SteveT, Dec 18, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. sck10
    Replies:
    3
    Views:
    7,847
    Steven Cheng[MSFT]
    Dec 26, 2005
  2. David Hubbard
    Replies:
    0
    Views:
    4,496
    David Hubbard
    Mar 1, 2006
  3. Ian
    Replies:
    3
    Views:
    16,495
    zpradipta
    Jul 23, 2007
  4. David Thielen

    ObjectDataSource method as another ObjectDataSource

    David Thielen, Mar 21, 2006, in forum: ASP .Net Web Controls
    Replies:
    3
    Views:
    246
    Steven Cheng[MSFT]
    Mar 23, 2006
  5. Replies:
    0
    Views:
    141
Loading...

Share This Page