Greetings super experts,
I am having some issues with this function.
We are presenting users a dropdownlist box to select a criterium for searching for data.
Inside the dropdownlist, a search can choose to search by name, email or date range.
If a user chooses any of the options, a search box for that option is displayed.
User can then enter his/search input and search.
On the VB, we are trying to present user with data based on his/her search criterium by using IF conditional.
If user wishes to searh by date range, the quey for the search criterium is presented.
Same with name and email.
The issues is we are getting the following error:
Error: System.Web.Query is a namespace and cannot be used as an expression and the error is on this line:
Dim cmd As SqlCommand = New SqlCommand(Query)
Below is the code:
HTML
<asp:DropDownList ID="ddlSearchCriteria" runat="server" AutoPostBack="true" style="background-color:#0093B2;border: 2px solid #92b200;width:375px;color:white;border-width:5px;" class="form-control select2-selection select2-selection--single form-control input-lg ddl" role="combobox" aria-haspopup="true" aria-expanded="false" aria-labelledby="select2-e8ez-container" OnSelectedIndexChanged="ddlSearchCriteria_Changed">
<asp:ListItem Value="" Text="Choose search criteria..."></asp:ListItem>
<asp:ListItem Value="1" Text="Search by Name"></asp:ListItem>
<asp:ListItem Value="2" Text="Search by Date Range"></asp:ListItem>
<asp:ListItem Value="3" Text="Search by Email"></asp:ListItem>
</asp:DropDownList><br /><br />
<div style="margin-left:10px;">
<asp:Panel ID="pnlbydaterange" runat="server" Visible="false">
From:<asp:TextBox ID="txtFrom" runat="server"></asp:TextBox>
To:<asp:TextBox ID="txtTo" runat="server"></asp:TextBox>
</asp:Panel>
<asp:Panel ID="pnlbyname" runat="server" Visible="false">
Authorized report's name:<asp:TextBox ID="txtempname" runat="server"></asp:TextBox>
</asp:Panel>
<asp:Panel ID="pnlbyemail" runat="server" Visible="false">
Authorized report's email:<asp:TextBox ID="txtemail" runat="server"></asp:TextBox>
</asp:Panel>
<asp:Button ID="Search" runat="server" Text="Search" OnClick="Search_Click" />
<asp:Button ID="btnClear" runat="server" height="26px" Text="Clear" onclientclick="return ClearSearch();" />
VB:
'Select search criteria
Protected Sub ddlSearchCriteria_Changed(ByVal sender As Object, ByVal e As EventArgs)
If ddlSearchCriteria.SelectedItem.Value = 1 Then
pnlbyname.Visible = True
pnlbydaterange.Visible = False
pnlbyemail.Visible = False
ElseIf ddlSearchCriteria.SelectedItem.Value = 2 Then
pnlbydaterange.Visible = True
pnlbyname.Visible = False
pnlbyemail.Visible = False
ElseIf ddlSearchCriteria.SelectedItem.Value = 3 Then
pnlbyemail.Visible = True
pnlbyname.Visible = False
pnlbydaterange.Visible = False
Else
End If
End Sub
' Get data from database.
Private Function GetData() As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
If ddlSearchCriteria.SelectedItem.Value = 1 Then
Dim query As String = "SELECT e.EmployeeID, Upper(e.empID) empID, e.employeeName,e.empTitle, e.email,convert(varchar, dateadd(hour, 0, datecreated), 100) dateCreated "
query += " From Employees e "
query += " INNER Join dateDetails d ON e.EmployeeID = d.employeeID "
query += " WHERE cast(CONVERT(NCHAR(8),d.dateCreated,112) AS int) >= 20250411 and cast(CONVERT(NCHAR(8),d.dateCreated,112) AS int) < 20250509 "
query += " employeeName LIKE '% @empname %' "
query += " AND YEAR(d.dateCreated) = 2025 AND e.employeeID NOT IN (850,851) "
query += " ORDER by d.dateCreated DESC "
ElseIf ddlSearchCriteria.SelectedValue = 2 Then
Dim query As String = " SELECT e.EmployeeID, Upper(e.empID) empID, e.employeeName,e.empTitle, e.email,convert(varchar, dateadd(hour, 0, datecreated), 100) dateCreated "
query += " From Employees e "
Query += " INNER Join dateDetails d ON e.EmployeeID = d.employeeID "
Query += " WHERE cast(CONVERT(NCHAR(8),d.dateCreated,112) AS int) >= 20250411 and cast(CONVERT(NCHAR(8),d.dateCreated,112) AS int) < 20250509 "
Query += " AND (cast(CONVERT(VARCHAR(10),d.dateCreated,101) AS DATETIME)>= @From "
Query += " AND cast(CONVERT(VARCHAR(10),d.dateCreated,101) AS DATETIME)<= @To) OR (@From IS NULL OR @To IS NULL) "
Query += " AND YEAR(d.dateCreated) = 2025 AND e.employeeID NOT IN (850,851) "
query += " ORDER by d.dateCreated DESC "
ElseIf ddlSearchCriteria.SelectedValue = 3 Then
Dim query As String = " SELECT e.EmployeeID, Upper(e.empID) empID, e.employeeName,e.empTitle, e.email,convert(varchar, dateadd(hour, 0, datecreated), 100) dateCreated "
query += " From Employees e "
Query += " INNER Join dateDetails d ON e.EmployeeID = d.employeeID "
Query += " WHERE cast(CONVERT(NCHAR(8),d.dateCreated,112) AS int) >= 20250411 and cast(CONVERT(NCHAR(8),d.dateCreated,112) AS int) < 20250509 "
query += " AND email = @email "
query += " AND YEAR(d.dateCreated) = 2025 AND e.employeeID NOT IN (850,851) "
query += " ORDER by d.dateCreated DESC"
Else
End If
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand(Query)
cmd.Parameters.AddWithValue("@From", If(Not String.IsNullOrEmpty(txtFrom.Text.Trim()), txtFrom.Text.Trim(), CObj(DBNull.Value)))
cmd.Parameters.AddWithValue("@To", If(Not String.IsNullOrEmpty(txtTo.Text.Trim()), txtTo.Text.Trim(), CObj(DBNull.Value)))
cmd.Parameters.AddWithValue("@empname", If(Not String.IsNullOrEmpty(txtempname.Text.Trim()), txtempname.Text.Trim(), CObj(DBNull.Value)))
cmd.Parameters.AddWithValue("@email", If(Not String.IsNullOrEmpty(txtemail.Text.Trim()), txtemail.Text.Trim(), CObj(DBNull.Value)))
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
I don't know what I am doing wrong. Your assistance is greatly appreciated
