ic3man7019 ic3man7019 - 23 days ago 5
Vb.net Question

How would I go about loading result sets from separate SQL Server Database queries into a GridView?

I have to accomplish what seemed like a pretty trivial task, but it has proven to be a bit more confusing than I anticipated. Here is the scenario:

I have a

GridView
that is getting data from my
SQL Server
database and displaying it on a web page (a pretty normal scenario). However, I have some filters that determine which query is run on the database, which obviously determines the data that is ultimately displayed in the
GridView
. I am having a bit of trouble incorporating these filters efficiently. Currently, I have these three subroutines:
LoadPendingRequests()
,
LoadApprovedRequests()
, and
LoadDeniedRequests()
. As you might expect, I have a
CheckboxList
that allows the user to choose "Pending," "Approved," or "Denied," and, depending on which is chosen, the corresponding subroutine is called, thus populating the
GridView
with the corresponding data. This part works.

The user has the option to load any combination of pending, denied, and approved requests at the same time, however. This is where I'm having trouble. Here is the
SelectedIndexChanged
event handler for the
CheckboxList
:

Protected Sub CheckboxList1_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles CheckboxList1.SelectedIndexChanged

If CheckboxList1.SelectedIndex < 0 Then
CheckboxList1.SelectedIndex = 0
End If


If CheckboxList1.Items(0).Selected = True Then 'pending checkbox
LoadPendingRequests()
End If
If CheckboxList1.Items(1).Selected = True Then 'approved checkbox
LoadApprovedRequests()
End If
If CheckboxList1.Items(2).Selected = True Then 'denied checkbox
LoadDeniedRequests()
End If
End Sub


Here is an example of the subroutines that load the requests (they're all the same, just replace the filter word in the "where" clause):

Protected Sub LoadPendingRequests()
Try
Dim DbConnection As New DevConnection()
Dim Da As New SqlDataAdapter()

Using DbConnection.Conn
DbConnection.Conn.Open()
Using SqlCmd As New SqlCommand()
With SqlCmd
.Connection = DbConnection.Conn
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM dbo.reqTable where Status = 'Pending'"
End With
Using Da
Da.SelectCommand = SqlCmd
Using Dt As New DataTable()
Da.Fill(Dt) 'populates the dataset
Gv.DataSource = Dt
'Gv.DataBind()

CheckSuggestionApprovalStatus()
End Using
End Using
End Using
End Using
Catch ex As Exception

End Try

End Sub


How would I go about loading pending, approved, and denied requests at the same time when the user clicks on the boxes in the
CheckboxList
, given this setup? I am open to changing this process entirely if necessary.

Answer

Here is how a stored procedure for this type of thing might look.

create procedure Requests_GetByStatus
(
    @Status varchar(10)
) as
    set nocount on;

    select *
    from dbo.reqTable
    where Status = @Status

I would urge you to not store the status as text in every row. I would instead have a RequestStatus table that holds the text value and an int (likely an identity) and store that value in your Request table. And of course don't really use select *, you should specify the columns you actually need.

--EDIT--

This is one way you can pass in three parameters and get the data in any group in a single query. I have expanded the where predicates significantly so you can easily see what is happening.

create procedure Requests_GetByStatus
(
    @Pending bit = null
    , @Approved bit = null
    , @Denied bit = null
) as
    set nocount on;

    select *
    from dbo.reqTable
    where 
    (
        (
            Status = 'Pending' 
            AND
            @Pending = 1
        )
    )
    OR
    (
        (
            Status = 'Approved'
            AND
            @Approved = 1
        )
    )
    OR
    (
        (
            Status = 'Denied'
            AND
            @Denied = 1
        )
    )