Dynamic Where clause is failing

Question

User1216627406 posted

Greetings experts,

Have several input parameters I am using to filter records.

In the code below, I am using just two to show what I am doing.

Two input params, name and senders_email.

When a user performs a search and enters value, say for senders_email, then in my code, since name is empty, ignore it and perform search based only on the values entered for senders_email. Same is the case for when senders_email is empty, ignore it and perform search for values entered for name only.

So far, my query is failing.

I keep getting error: Incorrect syntax near the keyword 'OR'. 

Any ideas what I am doing wrong?

    Private Sub BindGridData()
        Dim conString As String = ConfigurationManager.ConnectionStrings("ppmtest").ConnectionString
        Dim conn As New SqlConnection(conString)
        Dim s As String = ""

        conn.Open()
        Dim cmd As New SqlCommand(s)
        Dim whereclause = ""
        If empname.Text <> "" Then
            whereclause += " OR name LIKE '%' + @name + '%'"
        End If
        If empemail.Text <> "" Then
            whereclause += " OR senders_email = LIKE '%' + @email + '%'"
        End If
        cmd = New SqlCommand("SELECT work_request_no, senders_email, teca, date_employee_assigned, assignedToShop,[date],[time],[name],[phone],[description], [completionDate], [CompletionHours], [PercentCompltAW], [ActualWorkPerformed] from work_request " &
            "WHERE " & whereclause & "ORDER By [date] DESC ", conn)
        cmd.Parameters.AddWithValue("@name", empname.Text)
        cmd.Parameters.AddWithValue("@email", empemail.Text)
        Dim dt As New DataTable()
        Using sda As New SqlDataAdapter(cmd)
            sda.Fill(dt)
            gvInPerson.DataSource = dt
            gvInPerson.DataBind()
        End Using
        'Showing Numbers in Label
        Dim iTotalRecords As Integer = DirectCast(gvInPerson.DataSource, DataTable).Rows.Count
        Dim iEndRecord As Integer = gvInPerson.PageSize * (gvInPerson.PageIndex + 1)
        Dim iStartsRecods As Integer = iEndRecord - gvInPerson.PageSize

        If iEndRecord > iTotalRecords Then
            iEndRecord = iTotalRecords
        End If

        If iStartsRecods = 0 Then
            iStartsRecods = 1
        End If
        If iEndRecord = 0 Then
            iEndRecord = iTotalRecords
        End If
        Message.Text = "Total Requests Found: <strong>" & iTotalRecords.ToString() & "</strong>"
    End Sub

Many thanks in advance

Answer 1

User1216627406 posted

I have resolved my problem:

Imports System.Data
Imports System.Linq
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.IO

Partial Class searchPage
    Inherits System.Web.UI.Page
    Dim ename As String = ""
    Dim email As String = ""

    'Define connection string globally
    Dim conString As String = ConfigurationManager.ConnectionStrings("ppmtest").ConnectionString
    Dim conn As New SqlConnection(conString)
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ' Me.BindGridData()
        End If
    End Sub
    Private Sub BindGridData()
        Dim conString As String = ConfigurationManager.ConnectionStrings("ppmtest").ConnectionString
        Dim conn As New SqlConnection(conString)
        Dim s As String = ""

        conn.Open()
        Dim cmd As New SqlCommand(s)
        Dim whereclause = ""

        whereclause = "name LIKE '%' + @name + '%' and senders_email LIKE '%' + @email + '%' ORDER BY [date] DESC"
        cmd = New SqlCommand("SELECT work_request_no, senders_email, teca, date_employee_assigned, assignedToShop,[date],[time],[name],[phone],[description], [completionDate], [CompletionHours], [PercentCompltAW], [ActualWorkPerformed] from work_request " &
            "WHERE " & whereclause, conn)
        cmd.Parameters.AddWithValue("@name", empname.Text)
        cmd.Parameters.AddWithValue("@email", empemail.Text)
        Dim dt As New DataTable()
        Using sda As New SqlDataAdapter(cmd)
            sda.Fill(dt)
            gvInPerson.DataSource = dt
            gvInPerson.DataBind()
        End Using
        'Showing Numbers in Label
        Dim iTotalRecords As Integer = DirectCast(gvInPerson.DataSource, DataTable).Rows.Count
        Dim iEndRecord As Integer = gvInPerson.PageSize * (gvInPerson.PageIndex + 1)
        Dim iStartsRecods As Integer = iEndRecord - gvInPerson.PageSize

        If iEndRecord > iTotalRecords Then
            iEndRecord = iTotalRecords
        End If

        If iStartsRecods = 0 Then
            iStartsRecods = 1
        End If
        If iEndRecord = 0 Then
            iEndRecord = iTotalRecords
        End If
        Message.Text = "Total Requests Found: <strong>" & iTotalRecords.ToString() & "</strong>"
    End Sub
    Protected Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        'Set value of the search strings, fromDate and ToDate
        ename = empname.Text
        email = empemail.Text
        Me.BindGridData()
    End Sub
    Protected Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        ' Simple clean up text to return the Gridview to it's default state
        empname.Text = ""
        empemail.Text = ""
        Me.BindGridData()
    End Sub

    Protected Sub gvInPerson_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles gvInPerson.PageIndexChanging
        gvInPerson.PageIndex = e.NewPageIndex
        Me.BindGridData()
        gvInPerson.DataBind()
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)

        ' Verifies that the control is rendered 

    End Sub

End Class

This works perfectly for me.

Answer 2

User475983607 posted

Rather than rendering dynamic SQL try this binary short cut method.

DECLARE @LastName VARCHAR(64) = 'Gee'
DECLARE @Email VARCHAR(128) = NULL

SELECT [CustomerID]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[CompanyName]
      ,[SalesPerson]
      ,[EmailAddress]
      ,[Phone]
      ,[PasswordHash]
      ,[PasswordSalt]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [SalesLT].[Customer]
  WHERE ([LastName] LIKE '%' + @LastName + '%' OR ISNULL(@LastName, '1') = '0')
		OR 
		([EmailAddress] LIKE '%' + @Email + '%' OR ISNULL(@Email, '1') = '0')

Otherwise the issue with your code is the whereclause always starts with "OR".  You end up with...

WHERE OR ...

Or substring the whereclause to remove the first "OR"

whereclause = whereclause.Substring(4)

I generally run the code through the debugger to fetch the generated SQL then paste the SQL in SSMS to find the error.