CODEWITHSUNDEEP
vb.netms-access
CodingKev
CodingKev

Reputation: 3

No results when parameter is null

Hi I was wondering if anyone would be able to help me with a coding issue I am having.

I have a form where the user can enter a last name in a text box to return results from a database (access 2013) where the last name matches. This works fine, I would also like the query to return all the entries in the database if the text box is left blank. This is the part I am having a problem with, when the text box is left blank no results are returned. I have searched through several questions on this site and from what I can see so far my code should work. Could someone please tell me where my code is incorrect?

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    'creates a list of all employees as per the search parameters
    Dim dt As New DataTable
    Dim query As String = " select [employeeid] as empolyeeid, (firstname &' '& lastname &' '& dob) as empdetails from employee where ((@lastname is null) or (lastname = @lastname))"
    Using connection As New OleDbConnection(My Database Connection)
        Using command As New OleDbCommand(query, connection)
            Using adapter As New OleDbDataAdapter(command)
                command.Parameters.AddWithValue("@lastname", txt_lastname.Text)
                connection.Open()
                adapter.Fill(dt)
                connection.Close()
            End Using
        End Using
    End Using

    If dt.Rows.Count > 0 Then
        lb_search.DataSource = dt
        lb_search.DisplayMember = "employeeid"
        lb_search.ValueMember = "empdetails"
    End If
End Sub

Thanks for reading. Would really appreciate any insight as to why this isnt working thanks

Upvotes: 0

Views: 54

Answers (2)

Steve
Steve

Reputation: 216293

The problem is that a TextBox.Text property is never null.
When you leave the TextBox blank in reality you have a Text=String.Empty, not a null.

Of course the solution is to check for a blank string instead of null

Dim query As String = "select [employeeid] as empolyeeid, " & _ 
                    "(firstname &' '& lastname &' '& dob) as empdetails " & _
                    "from employee where ((@lastname = '') or " & _ 
                    "(lastname = @lastname))"

but you could also simplify your command text and change your VB code to

Dim query As String = "select [employeeid] as empolyeeid, " & _ 
                    "(firstname &' '& lastname &' '& dob) as empdetails " & _
                    "from employee where lastname LIKE @lastname"

command.Parameters.AddWithValue("@lastname", 
         if(string.IsNullOrWhiteSpace(txt_lastname.Text), 
            "%", txt_Lastname.Text.Trim())

In this way there is only one condition on the WHERE clause and the match is done using the LIKE operator. In this way, in case your textbox is blank you could pass the wildcard % that matches any lastname while, in case of an effective lastname, you could match the lastname field also if the value has been typed using a different case (lower/upper mismatch)

Upvotes: 1

Tarik
Tarik

Reputation: 11209

You should replace

((@lastname is null) or (lastname = @lastname))

With

((@lastname = '') or (lastname = @lastname))

Your text box can contain an empty string but not a null.

To be safe use txt_lastname.Text.Trim

Upvotes: 1

Related Questions