database search function

Question

i want to search a record from sql database searching by first name so im using a function in the data layer but it is not working please correct me where i went wrong here is my function:

Public Function searchCustomer(ByVal custFname As String) As DataTable
            Dim tabletdata As New DataTable
            Dim conn As New SqlConnection(con_string)
            conn.Open()
            Dim dCmd As New SqlCommand("selectCustomerByFname", conn)
            dCmd.CommandType = CommandType.StoredProcedure
            Try
                dCmd.Parameters.AddWithValue("@Cust_Fnam", custFname)
                'dCmd.ExecuteNonQuery()
                Dim dadaptr As New SqlDataAdapter(dCmd)
                dadaptr.SelectCommand = dCmd
                dadaptr.SelectCommand.ExecuteNonQuery()
                dadaptr.Fill(tabletdata)


                Return tabletdata
            Catch
                Throw
            Finally
                dCmd.Dispose()
                conn.Close()
                conn.Dispose()
            End Try

        End Function

Answer 1

Fill method opens and close connection implicitly. Fill Method

SUMMARY: The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

Public Function searchCustomer(ByVal custFname As String) As DataTable
  Dim tabletdata As New DataTable
  Dim conn As New SqlConnection(con_string)
  Dim dCmd As New SqlCommand("selectCustomerByFname", conn)
  dCmd.CommandType = CommandType.StoredProcedure

  dCmd.Parameters.AddWithValue("@Cust_Fnam", custFname)
  Dim dadaptr As New SqlDataAdapter(dCmd)
  dadaptr.SelectCommand = dCmd
  dadaptr.Fill(tabletdata)
  Return tabletdata
End Function