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
Protected Sub CheckboxList1_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles CheckboxList1.SelectedIndexChanged
If CheckboxList1.SelectedIndex < 0 Then
CheckboxList1.SelectedIndex = 0
If CheckboxList1.Items(0).Selected = True Then 'pending checkbox
If CheckboxList1.Items(1).Selected = True Then 'approved checkbox
If CheckboxList1.Items(2).Selected = True Then 'denied checkbox
Protected Sub LoadPendingRequests()
Dim DbConnection As New DevConnection()
Dim Da As New SqlDataAdapter()
Using SqlCmd As New SqlCommand()
.Connection = DbConnection.Conn
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM dbo.reqTable where Status = 'Pending'"
Da.SelectCommand = SqlCmd
Using Dt As New DataTable()
Da.Fill(Dt) 'populates the dataset
Gv.DataSource = Dt
Catch ex As Exception
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.
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 ) )