0

I have the following DropDownList :

            <asp:DropDownList ID="GenderDropDownList" runat="server">
                <asp:ListItem>Gender</asp:ListItem>
                <asp:ListItem>MAle</asp:ListItem>
                <asp:ListItem>Female</asp:ListItem>
            </asp:DropDownList>

and Its connected to sqlserver database table called Staff where it has (Gender) field with type (bit) , true means male and false means female. what I want is to filter using @gender parameter here is the code behind in VB.net:

    StaffSqlDataSource.SelectCommand = " select Id,FullName,case when Gender = 'true'  then 'male' else 'female' end as Gender, JobTitle,PhoneNumber,Notes from Staff  where (REPLACE(FullName,' ','') LIKE '%' + REPLACE(@FullName,' ','') + '%' OR @FullName='Null') and (Gender=@Gender or @Gender='Null') order by id desc"


    StaffSqlDataSource.SelectParameters.Clear()

    'Search By FullName
    If FullNameTextBox.Text.Trim = "" Then
        StaffSqlDataSource.SelectParameters.Add("FullName", "Null")
    Else
        StaffSqlDataSource.SelectParameters.Add("FullName", FullNameTextBox.Text.Trim)
    End If

    'Search By Gender
    If GenderDropDownList.SelectedIndex = 0 Then
        StaffSqlDataSource.SelectParameters.Add("Gender", "Null")
    ElseIf GenderDropDownList.Text.Trim = "male" Then
        StaffSqlDataSource.SelectParameters.Add("Gender", True)
    Else
        StaffSqlDataSource.SelectParameters.Add("Gender", False)
    End If

    StaffGridView.DataSourceID = "StaffSqlDataSource"
    StaffGridView.DataBind()

the problem is it shows this Error: 'Conversion failed when converting the nvarchar value 'Null' to data type bit. what can i do??

3
  • Why pass True or False and then use a CASE in your SQL when you can just pass "male" or "female" in the first place? Commented Feb 9, 2020 at 11:20
  • Hello Using a bit type field for gender instead of varchar can save lots of data storage if you have millions of records Commented Feb 9, 2020 at 12:17
  • I think that the problem is in your "Null" parameter value try to use DBNull.value : If GenderDropDownList.SelectedIndex = 0 Then StaffSqlDataSource.SelectParameters.Add("Gender", DBNull.value) Commented Feb 10, 2020 at 11:29

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.