I just completed my first real .net application and am trying to make it more effiicient by breaking up huge blocks of code into their own procedures, functions, etc.

This should be a simple question for those of you who aren't newbies like myself.  In the following code, I can not get my string to return as desired. What's wrong with this?

Protected Sub bt_search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bt_search.Click
        Dim sqlwhere As String
        
        //other code
        
        where_cl()

        //other code

End Sub

Public Function where_cl() as string
  Dim sqlwhere as string

          'Build the WHERE clause based on what the user enters as search criteria. This is handled in a hierarchial order since the user
        ' may enter various permetuations in the search criteria.
        If tx_lastname.Text <> "" And tx_firstname.Text <> "" And tx_middlename.Text <> "" And tx_ocanumber.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                      " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                    " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                    " OR dob = '" & tx_dateofbirth.Text & "'" & _
                                    " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_firstname.Text <> "" And tx_ocanumber.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                    " OR dob = '" & tx_dateofbirth.Text & "'" & _
                                    " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_middlename.Text <> "" And tx_ocanumber.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                    " OR dob = '" & tx_dateofbirth.Text & "'" & _
                                    " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_firstname.Text <> "" And tx_ocanumber.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                    " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_middlename.Text <> "" And tx_ocanumber.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                    " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_firstname.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" & _
                                    " OR dob = '" & tx_dateofbirth.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_middlename.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " AND mname like '" & Replace(tx_middlename.Text, "'", "''") & "%'" & _
                                    " OR dob = '" & tx_dateofbirth.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_ocanumber.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                    " OR oca = '" & tx_ocanumber.Text & "'" & _
                                    " OR dob = '" & tx_dateofbirth.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_ocanumber.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        ElseIf tx_lastname.Text <> "" And tx_dateofbirth.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "')"
            Else
                sqlwhere = " where (lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" & _
                                      " OR dob = '" & tx_dateofbirth.Text & "')"
            End If
        ElseIf tx_dateofbirth.Text <> "" And tx_ocanumber.Text <> "" Then
            If (InStr(sqlwhere, "where")) Then
                sqlwhere = sqlwhere & " AND (dob = '" & Replace(tx_dateofbirth.Text, "'", "''") & "'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            Else
                sqlwhere = " where (dob = '" & Replace(tx_dateofbirth.Text, "'", "''") & "'" & _
                                      " OR oca = '" & tx_ocanumber.Text & "')"
            End If
        End If
       Return sqlwhere
End function

What is it returning? Is it compiling? Is it returning anything?

Have you stepped through with the debuggr?

The first thing that occurs is you are not assigning teh returned value of your method to anything

Protected Sub bt_search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bt_search.Click
        Dim sqlwhere As String
       
        //other code
        Dim myvalue as String
       myValue =  where_cl()

        //other code

End Sub

 Also, think about moving all those queries out to stored procedures, it's not good practice to hard code queries in code, it is better to use parameterized stored procedures.

iainpb:

you are not assigning teh returned value of your method to anything

Thanks dude.  That fixed it.  I knew that my problem was not yet knowing the basic fundamentals for calling methods.

No problem, if you want to move out the queries to stored procedures (more maintainable mainly and slightly faster) here is a guide:

http://www.dotnetjunkies.ddj.com/Article/9AE62C44-3841-4687-B906-2F6D4A5A4622.dcik