Populating a gridview (.net 2.0) using objectdatasource

[Julia B]

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>
<asparameter 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]

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]

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

 

[SteveT]

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.