I'm not sure if the subject title makes sense, but it wasn't too easy to describe what I'm trying to do in one sentence!
When a user clicks on the delete link in a GridView row, I want to run a stored proc, using the Key of the selected record as the parameter for the stored proc. I then want to programatically access the result of the stored proc (it'll be an integer value) for code-behind use.
I wrote out a few steps to help me figure out what I need to do:1) Get the value of PersonnelID (the DataKey) from the selected row in the GridView
2) Set that PersonnelID value as the select parameter for the datasource dsPersonnelForSupervisorCheck"
3) Run the stored proc via the business logic class "PersonnelSystem"s function "GetListPersonnelForSupervisorCount" (all data handling is done through business logic classes)
4) Determine if the value from the stored proc is greater than 1
a) If so, display message box & cancel delete operation
b) If not, continue with delete operation
I'm assuming the best place to do this is in the GridView_RowDeleting event. My main sticking points are how to access the PersonnelID of the row selected for deletion, how to get that PersonnelID value set to the SelectParameter for the dsPersonnelForSupervisorCheck" datasource, and how to access the result of the stored proc. I think once I get those ironed out I can handle the business logic class handling and the value comparison/message box part.
Here's the VB code I've been "messing" around with. Hopefully my copious use of commenting will help clarify what exactly I'm trying to do:1 Protected Sub gvPersonnel_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles gvPersonnel.RowDeleting 2 3 'Define a variable to hold the PersonnelID value 4 Dim checkSelectParam As String 5 6 'Get the value of PersonnelID from the selected row in the GridView 7 checkSelectParam = dsPersonnel.DeleteParameters.ToString 'Mouseover at runtime shows checkSelectParam as "System.Web.UI.WebControls.ParameterCollection" 8 9 'Set the retrieved PersonnelID value (stored in checkSelectParam) as the select parameter for the supervisor check datasource 10 dsPersonnelForSupervisorCheck.SelectParameters(checkSelectParam).ToString() 11 'Above code generates the following error at runtime 12 'NullReferenceException was unhandled by user code 13 'Object reference not set to an instance of an object 14 15 'dsPersonnelForSupervisorCheck.SelectParameters("PersonnelID") = gvPersonnel.SelectedValue() 16 'Above generates following error at runtime 17 'ArgumentNullException was unhandled by user code 18 'Value cannot be null. 19 'Parameter(name) : value 20 21 'Select parameter is retrieved, so run the stored proc via the business logic 22 dsPersonnelForSupervisorCheck.DataBind() 23 24 'Define a variable to hold the result of the stored proc's select statement 25 Dim value As String 26 27 'Set "value" to the result of the stored proc 28 'value = 'No idea how to do this! 29 30 If value > 0 Then 31 'Above generates following error at runtime 32 'InvalidCastException was unhandled by user code 33 'Conversion from string "System.Web.UI.WebControls.Parame" to type 'Double' is not valid. 34 MsgBox("The personnel you selected for deletion" & ControlChars.NewLine & "is assigned as a supervisor for at least one other personnel." _ 35 & ControlChars.NewLine & "Personnel record cannot be deleted.", MsgBoxStyle.OkOnly, "Operation Canceled") 36 37 'Selected personnel is assigned as super to other personnel, so cancel delete 38 Exit Sub 39 Else 40 'Continue with row deletion 41 End If 42 End Sub
Here are the related datasources:<asp:ObjectDataSource ID="dsPersonnelForSupervisorCheck" runat="server" SelectMethod="GetListPersonnelForSupervisorCount" TypeName="AgCodeNET2.BusinessObject.PersonnelSystem"> <SelectParameters> <asp:Parameter Name="PersonnelID" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource> <asp:ObjectDataSource ID="dsPersonnel" runat="server" SelectMethod="GetListPersonnel" DeleteMethod="DeletePersonnel" UpdateMethod="UpdatePersonnel" InsertMethod="UpdatePersonnel" TypeName="AgCodeNET2.BusinessObject.PersonnelSystem"> <SelectParameters> <asp:Parameter Name="PayrollCompanyID" Type="int32" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="PersonnelID" Type="Int32" /> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="IsSupervisor" Type="Boolean" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="PersonnelID" Type="Int32" /> </DeleteParameters> </asp:ObjectDataSource>
Don't know if it'll help, but here's the stored proc that checks the table to see if the selected PersonnelID is found in the SupervisorID of other records in the table:CREATE PROCEDURE dbo.sp_CheckSupervisorCount @PersonnelID int AS DECLARE @count int SET @count = 0 SELECT @count = COUNT(SupervisorID) FROM rsrc_Personnel WHERE SupervisorID = @PersonnelID GO
I'd appreciate it if someone could take a look at my code and help me figure out how to accomplish what I'm trying to do.
"f u cn rd ths, u cn gt a gd jb n cmptr prgmmng." - Anon
Here's what I would do (I'm sure there are plenty of other ways to do it though):
Set the CommandArgument of the Delete button or whatever button you are using to the row index - I usually do this in the RowDataBound
Use the RowCommand event rather than the RowDeleting event
Check e.CommandName to check which command raised the event
Dim rowindex as integer = cint(e.CommandArgument)
Rather than using an ObjectDataSource you can use a table adapter to retrieve the row count
e.g. Dim dta as new (whatever your table adater is here): Dim dt as datatable = dta.GetListPersonnelForSupervisorCount(Me.gvPersonnel.DataKeys(rowindex).Value))
You can get the number of rows or if you are just returning a count, value for the first row
If Value = 0 then Me.gvPersonnel.DeleteRow(rowindex)
Hope that helps, Tim