Dropdownlist and search text box for searching to a grid

Honeyjo
 
on Aug 17, 2021 04:38 AM
491 Views

Hi,

Im creating a webform in vb.net. It uses grid view as one page. I would like the user to use a dropdownlist for m number, surname or status and use a search box if user select the m number or surname or a dropdownlist if user select status. 

once the user select either. i have created the stored procedures for each m number, status or surname to get the data from sql. 

I have added the html i used in the form. 

                        <asp:Label ID="Label" runat="server" Text="Select Filter:"></asp:Label>
                        <br />
                        <asp:DropDownList ID="ddlGridfilter" runat="server" DataSourceID="Gridfilter" DataTextField="Filter" DataValueField="Filter" AutoPostBack="True" AppendDataBoundItems="True">
                        <asp:ListItem Text="--Select--" Value="0"></asp:ListItem>
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="Gridfilter" runat="server" ConnectionString="<%$ ConnectionStrings:Database_Connection_Lookups %>" SelectCommand="SELECT * FROM [AutopsyGridFilter] ORDER BY [Filter]"></asp:SqlDataSource>
                    </td>
                    <td class="auto-style9" rowspan="2">
                        <asp:Button ID="btnsearch" runat="server" Text="Search" />
                    </td>
                </tr>
                <tr>
                    <td class="auto-style6">&nbsp;</td>
                    <td class="auto-style6">
                        <asp:Label ID="Label3" runat="server" Text="Search:"></asp:Label>
                        <asp:TextBox ID="txtsearch" runat="server" Height="16px" Visible="False" Width="92px" ></asp:TextBox>
                    </td>
                    <td class="auto-style7">
                        <asp:DropDownList ID="ddlstatussearch" runat="server" DataSourceID="status" DataTextField="RecordStatus" DataValueField="RecordStatus" Visible="False" AppendDataBoundItems="True" AutoPostBack="True">
                        <asp:ListItem Text="--Select--" Value="0"></asp:ListItem>
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="status" runat="server" ConnectionString="<%$ ConnectionStrings:Database_Connection_Lookups %>" SelectCommand="SELECT [RecordStatus] FROM [AutopsyRecordStatus]"></asp:SqlDataSource>
                    </td>
                </tr>

I would like to know how will i go about in vb to get this search work and show as a grid. 

Many Thanks

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Results 1 - 5 of 8
dharmendr
 
on Aug 17, 2021 04:42 AM

What is the filter condition?

Do you want to filter using AND or OR condition?

Honeyjo
 
on Aug 17, 2021 05:15 AM

Hello,

The filter will be like: 

 

User selects  M Number from first dropdown list and put tt123 in search textbox click search button and they will get the result in the grid. 

Same with Surname: User selects Surname from first dropdown list and put Smith in Search textbox click search buttona and they will get the result in the grid. 

If user selects Status from First dropdown list, they will get a 2nd dropdown instead of the text box and they will select complete or incomplete and this will show the relevant data in the grid. 

The below vb code is what I have used to show the search box or the second drop down.

 Protected Sub ddlGridfilter_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlGridfilter.SelectedIndexChanged
        If ddlGridfilter.SelectedItem.Value = "M Number" Then
            txtsearch.Visible = True
            ddlstatussearch.Visible = False
        ElseIf ddlGridfilter.SelectedItem.Value = "Surname" Then
            txtsearch.Visible = True
            ddlstatussearch.Visible = False

        Else ddlGridfilter.SelectedItem.Value = "Status"
            txtsearch.Visible = False
            ddlstatussearch.Visible = True


        End If

    End Sub

Hope this make sense.

Many Thanks

 

dharmendr
 
on Aug 17, 2021 07:46 AM

Share GridView html and sample record of database table.

Honeyjo
 
on Aug 18, 2021 02:23 AM

Hi,

My Gridview html

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="PMNo"  Width="100%" EmptyDataText="There are no records to display for selected date" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" GridLines="Horizontal" 
          >
             
            <AlternatingRowStyle BackColor="#F7F7F7" />
             
            <Columns>
                
                
                
                <asp:BoundField DataField="PMNo" HeaderText="PMNo" ReadOnly="True" SortExpression="PMNo" InsertVisible="False" />
                <asp:BoundField DataField="PMRef" HeaderText="PMRef" SortExpression="PMRef" />
                <asp:BoundField DataField="HospNo" HeaderText="HospNo" SortExpression="HospNo" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" />
                <asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" />
                <asp:CheckBoxField DataField="Inquest" HeaderText="Inquest" SortExpression="Inquest" />
                <asp:BoundField DataField="Source" HeaderText="Source" SortExpression="Source" />
                <asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
                <asp:BoundField DataField="PMDate" HeaderText="PMDate" SortExpression="PMDate" />
                <asp:BoundField DataField="Pathologist" HeaderText="Pathologist" SortExpression="Pathologist" />
                
                <asp:CommandField ShowSelectButton="True" />
            </Columns>
            <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
            <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
            <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
            <SortedAscendingCellStyle BackColor="#F4F4FD" />
            <SortedAscendingHeaderStyle BackColor="#5A4C9D" />
            <SortedDescendingCellStyle BackColor="#D8D8F0" />
            <SortedDescendingHeaderStyle BackColor="#3E3277" />
        </asp:GridView>

My Database table these goes: 

 

CREATE TABLE [dbo].[Abk](
	[PMNo] [int] IDENTITY(1,1) NOT NULL,
	[PMRef] [nvarchar](50) NULL,
	[PMDate] [date] NULL,
	[HospNo] [int] NULL,
	[AlsoKnownAs] [nvarchar](150) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[DOB] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[Source] [nvarchar](50) NULL,
	[Type] [nvarchar](50) NULL,
	[RecordedBy] [nvarchar](50) NULL,
	[Pathologist] [nvarchar](50) NULL,
	[Locum] [nvarchar](50) NULL,
	[LocumOther] [nvarchar](50) NULL,
	[HOPathologist] [nvarchar](50) NULL,
	[SpRorTrainee] [nvarchar](50) NULL,
	[PoliceName] [nvarchar](50) NULL,
	[PoliceNumber] [nvarchar](50) NULL,
	[Inquest] [bit] NULL,
	[Inquestconcluded] [date] NULL,
	[TissueTaken] [bit] NULL,
	[TissueStored] [bit] NULL,
	[DisposalDue] [date] NULL,
	[TissueDisposal] [date] NULL,
	[DisposalMethod] [nvarchar](50) NULL,
	[Cause1a] [nvarchar](max) NULL,
	[Cause1b] [nvarchar](max) NULL,
	[Cause1c] [nvarchar](max) NULL,
	[Cause2] [nvarchar](max) NULL,
	[EmailinSharedDrive] [bit] NULL,
	[CoroneremailNotes] [nvarchar](max) NULL,
	[RecordStatus] [nvarchar](100) NULL,
 CONSTRAINT [PK_Autopsybook] PRIMARY KEY CLUSTERED 
(
	[PMNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


Stored procedures used for M Number, Surname and Status: 

ALTER PROCEDURE [dbo].[usp_GetRecordbySurname]

(
@LastName nvarchar(50)
)

AS

SET NOCOUNT ON;

SELECT * 
FROM [dbo].[Autopsybook]
Where LastName = @LastName


GO


ALTER PROCEDURE [dbo].[usp_GetRecordbyMNumber]
(@PMRef nvarchar(50)
)
AS
SET NOCOUNT ON;
SELECT *
FROM [dbo].[Autopsybook]
Where PMRef = @PMRef


GO







ALTER PROCEDURE [dbo].[usp_GetAutopsyRecordStatus]
(@RecordStatus nvarchar(100)
)
AS
SET NOCOUNT ON;

Select * from [dbo].[Autopsybook]
Where  RecordStatus = @RecordStatus


GO



Hope this helps. 

dharmendr
 
on Aug 18, 2021 07:03 AM
on Aug 18, 2021 07:04 AM

Hi Honeyjo,

Refer below stored procedure.

CREATE PROCEDURE [dbo].[usp_GetRecordbySurnamePMRefStatus] 
(
	@LastName nvarchar(50) = NULL,
	@PMRef nvarchar(50) = NULL,
	@RecordStatus nvarchar(100) = NULL
) 
AS 
BEGIN
	SET NOCOUNT ON; 
	SELECT *
	FROM [dbo].[Autopsybook]
	Where (LastName = @LastName OR @LastName IS NULL) 
	AND (PMRef = @PMRef OR @PMRef IS NULL) 
	AND (ecordStatus = @RecordStatus OR @RecordStatus IS NULL)
END 
GO

Code

Protected Sub btnsearch_OnClick(sender As Object, e As EventArgs) Handles btnsearch.Click    
	Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
		Using cmd As SqlCommand = New SqlCommand("usp_GetRecordbySurnamePMRefStatus", con)
			cmd.CommandType = CommandType.StoredProcedure
			If Not String.IsNullOrEmpty(txtsearch.Text.Trim()) Then
				cmd.Parameters.AddWithValue("@LastName", txtsearch.Text.Trim())
			Else
				command.Parameters.AddWithValue("@LastName", DBNull.Value)
			End If
			If ddlGridfilter.SelectedItem.Value > 0 Then
				cmd.Parameters.AddWithValue("@PMRef", ddlGridfilter.SelectedItem.Text)
			Else
				command.Parameters.AddWithValue("@PMRef", DBNull.Value)
			End If
			If ddlstatussearch.SelectedItem.Value > 0 Then
				cmd.Parameters.AddWithValue("@RecordStatus", ddlstatussearch.SelectedItem.Text)
			Else
				command.Parameters.AddWithValue("@RecordStatus", DBNull.Value)
			End If
			Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
				Dim dt As DataTable = New DataTable()
				sda.Fill(dt)
				Me.gvEmployee.DataSource = dt
				Me.gvEmployee.DataBind()
			End Using
		End Using
	End Using
End Sub

For more details refer below link.

Filter GridView based on multiple CheckBoxList like Flipkart in ASP.Net

Results 1 - 5 of 8