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??
TrueorFalseand then use aCASEin your SQL when you can just pass "male" or "female" in the first place?"Null"parameter value try to useDBNull.value: If GenderDropDownList.SelectedIndex = 0 Then StaffSqlDataSource.SelectParameters.Add("Gender", DBNull.value)